如何根据特定值组合为分组行分配Flag标识

问题分析 #

需求明确：

• 同一ID分组内同时包含C712和C751时，组内所有行Flag=2
• 仅包含C712无C751时，组内所有行Flag=1

原代码的问题 #

1. 第一个CASE逻辑错误：
   • Codes = 'C712' and Codes<> 'C751'属于冗余判断，单行Codes不可能同时满足两个矛盾条件，实际仅统计了C712的行数。
   • 后续判断when 2 then 1会漏掉只有1条C712的分组（如示例中的ID-001），导致返回0而非预期的1。
2. 第二个CASE逻辑错误：
   • 误将判断字段写成ID，实际应该是Codes。
   • 用sum统计时，若分组内存在多个重复的C712或C751，sum结果会大于2，导致when 2 then 2无法触发。

优化后的解决方案 #

方案1：基于MAX窗口函数（兼容性最好） #

通过窗口函数标记每个分组是否包含目标Codes，再根据标记设置Flag：

SELECT 
    ID,
    Codes,
    CASE
        WHEN has_c712 = 1 AND has_c751 = 1 THEN 2
        WHEN has_c712 = 1 AND has_c751 = 0 THEN 1
        ELSE 0 -- 无C712的情况可根据需求调整
    END AS Flag
FROM (
    SELECT 
        ID,
        Codes,
        -- 标记分组是否包含C712
        MAX(CASE WHEN Codes = 'C712' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_c712,
        -- 标记分组是否包含C751
        MAX(CASE WHEN Codes = 'C751' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS has_c751
    FROM your_table
) AS sub;

方案2：基于COUNT窗口函数（简洁写法） #

利用COUNT(DISTINCT)统计分组内目标Codes的唯一数量，注意部分数据库（如MySQL 5.x）不支持窗口函数中使用COUNT(DISTINCT)：

SELECT 
    ID,
    Codes,
    CASE
        -- 同时包含两个目标Codes
        WHEN COUNT(DISTINCT CASE WHEN Codes IN ('C712', 'C751') THEN Codes END) OVER (PARTITION BY ID) = 2 THEN 2
        -- 仅包含C712
        WHEN COUNT(CASE WHEN Codes = 'C712' THEN 1 END) OVER (PARTITION BY ID) > 0 THEN 1
        ELSE 0
    END AS Flag
FROM your_table;

说明 #

• 两种方案都通过窗口函数实现了分组内的全局判断，确保同一ID下所有行的Flag一致。
• 若存在其他特殊场景（如需要排除某些Codes），可在内部CASE的条件中补充过滤逻辑。

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