Oracle中带AND多条件的CASE WHEN语句修正请求

现有数据 #

ID effectivedate enddate
101. 6/1/2023.   6/30/2023
101. 7/1/2023.   7/31/2023
101. 8/1/2023.   8/31/2024
102. 5/1/2023.   5/31/2023

当前错误的CASE WHEN语句 #

Case 
    when effectivedate not between to_date('07/01/2023', 'mm/dd/yyyy') 
                               and to_date('07/31/2023', 'mm/dd/yyyy') 
     and effectivedate between to_date('08/01/2023', 'mm/dd/yyyy') 
                           and to_date('08/31/2023', 'mm/dd/yyyy') 
        then 'yes' 
        else 'no'

需求与判定规则 #

• ID=101不能存在2023年7月1日至7月31日的生效日期
• 目标ID需存在2023年8月的生效日期
• 判定规则：
  • 若ID同时存在7月和8月的生效日期，返回'no'
  • 若ID仅存在8月生效日期，返回'yes'

问题原因 #

原语句是逐行判断单条记录，ID101存在8月的单条记录，所以该行返回'yes'，但需求是基于ID的全局判断——只要该ID有7月的记录，无论是否有8月记录都要返回'no'，原逻辑未考虑全局情况，导致错误。

修正后的语句 #

方案1：按ID聚合后判断（返回每个ID的结果） #

SELECT 
    ID,
    CASE 
        WHEN has_july = 1 THEN 'no'
        WHEN has_august = 1 THEN 'yes'
        ELSE 'no'
    END AS result
FROM (
    SELECT 
        ID,
        -- 标记该ID是否存在7月生效记录
        MAX(CASE WHEN effectivedate BETWEEN TO_DATE('07/01/2023', 'mm/dd/yyyy') AND TO_DATE('07/31/2023', 'mm/dd/yyyy') THEN 1 ELSE 0 END) AS has_july,
        -- 标记该ID是否存在8月生效记录
        MAX(CASE WHEN effectivedate BETWEEN TO_DATE('08/01/2023', 'mm/dd/yyyy') AND TO_DATE('08/31/2023', 'mm/dd/yyyy') THEN 1 ELSE 0 END) AS has_august
    FROM your_table_name
    GROUP BY ID
) t;

方案2：保留原行数据，用窗口函数全局判断 #

SELECT 
    ID,
    effectivedate,
    enddate,
    CASE 
        -- 只要ID存在7月记录，返回'no'
        WHEN MAX(CASE WHEN effectivedate BETWEEN TO_DATE('07/01/2023', 'mm/dd/yyyy') AND TO_DATE('07/31/2023', 'mm/dd/yyyy') THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1 THEN 'no'
        -- 无7月记录但有8月记录，返回'yes'
        WHEN MAX(CASE WHEN effectivedate BETWEEN TO_DATE('08/01/2023', 'mm/dd/yyyy') AND TO_DATE('08/31/2023', 'mm/dd/yyyy') THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1 THEN 'yes'
        -- 其他情况返回'no'
        ELSE 'no'
    END AS result
FROM your_table_name;

说明 #

修正后的逻辑先对每个ID的所有记录做全局统计：

1. 标记该ID是否存在7月生效记录（has_july）
2. 标记该ID是否存在8月生效记录（has_august）
3. 按规则判断：只要has_july=1就返回'no'；仅当has_july=0且has_august=1时返回'yes'，其余情况返回'no'。

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