基于日期条件的补充剂ID分类：SQL逻辑修正需求

原始数据 #

补充说明：补充剂AB由补充剂A和B组成，ID可同时服用多种补充剂（例如ID 11同时服用A和AB）。

分类与日期提取规则 #

• B或AB的服用日期必须早于C的服用日期（或C为null）。
• 若服用了B，则需在B之前或同时服用A或AB：若AB早于B服用，取AB的日期；若A早于B服用，取B的日期。

尝试的SQL代码（未达预期） #

SELECT *,
    CASE
        WHEN (AB IS NOT NULL AND (AB <= B OR B IS NULL)) THEN AB
        WHEN (A IS NOT NULL AND (A <= B OR B IS NULL)) THEN B
        ELSE NULL
    END AS SupplementDate
FROM Table1
WHERE (B IS NOT NULL OR AB IS NOT NULL) 
AND (C IS NULL OR (B IS NOT NULL AND (A IS NOT NULL OR AB IS NOT NULL)))
ORDER BY ID;

预期结果 #

解决方案 #

原代码存在两处核心问题：

1. WHERE条件过滤逻辑错误，漏掉了仅服用AB但C不为null的合法情况，同时错误限制了B必须非null才检查C的条件；
2. CASE语句未完整覆盖规则，未将「日期需早于C」的判断纳入逻辑，也未处理AB晚于B时的优先级判断。

以下是修正后的SQL代码：

SELECT 
    ID,
    A,
    AB,
    B,
    C,
    CASE
        -- 优先处理AB的合法情况：AB存在且早于C（或C为null）
        WHEN AB IS NOT NULL AND (C IS NULL OR AB < C) THEN
            CASE
                -- 若同时有B且AB早于等于B，直接取AB
                WHEN B IS NOT NULL AND AB <= B THEN AB
                -- 若无B，直接取AB
                WHEN B IS NULL THEN AB
                -- 若AB晚于B，检查B是否符合规则（A早于等于B且B早于C）
                ELSE 
                    CASE
                        WHEN A IS NOT NULL AND A <= B AND (C IS NULL OR B < C) THEN B
                        ELSE NULL
                    END
            END
        -- 处理无AB但B合法的情况：B存在、A早于等于B、B早于C（或C为null）
        WHEN B IS NOT NULL AND A IS NOT NULL AND A <= B AND (C IS NULL OR B < C) THEN B
        -- 其他情况返回null
        ELSE NULL
    END AS SupplementDate
FROM Table1
ORDER BY ID;

逻辑说明 #

1. 优先判断AB的有效性：AB存在且满足日期早于C（或C为null）；
   • 若同时有B且AB早于等于B，直接取AB；
   • 若无B，直接取AB；
   • 若AB晚于B，则检查B是否符合规则，符合则取B，否则返回null；
2. 再判断B的有效性：B存在、A早于等于B、且B满足日期早于C（或C为null），符合则取B；
3. 所有不符合条件的场景返回null。

该逻辑完全覆盖给定规则，可得到预期结果。

内容的提问来源于stack exchange，提问作者geek45