多数据行下按Client分组检查Status同时含a和d的SQL查询问题

原始数据表结构 #

client_id   Status
   1         a
   1         b
   1         d
   1         e
   2         a
   2         d
   3         a
   3         b
   3         e
   4         a
   4         d
   5         b

需求说明 #

按client_id分组，判断每个客户的Status是否同时包含'a'和'd'，生成a_d_Flag字段：满足条件显示'Yes'，否则显示'No'，期望结果如下：

client_id     a_d_Flag
  1             Yes
  2             Yes
  3             No
  4             Yes
  5             No

错误的SQL代码 #

SELECT c.client
    , CASE WHEN c.Status = 'a' AND c.status = 'd'
    THEN 'Yes'
    ELSE 'No'
    END AS a_d_Flag
FROM client_details c
GROUP BY c.client
HAVING c.Status = 'a' AND c.status = 'd'

错误原因分析 #

1. 分组后直接引用c.Status属于非聚合列，逻辑上同一分组下Status有多个值，不可能同时等于'a'和'd'。
2. HAVING子句条件错误，单个行的Status无法同时满足等于'a'和'd'，会直接过滤掉所有数据。

修正后的SQL方案 #

方案一：用聚合函数统计状态覆盖情况 #

SELECT 
    client_id,
    CASE 
        WHEN COUNT(DISTINCT CASE WHEN Status IN ('a', 'd') THEN Status END) = 2 
        THEN 'Yes' 
        ELSE 'No' 
    END AS a_d_Flag
FROM client_details
GROUP BY client_id;

方案二：用SUM函数分别判断两种状态是否存在 #

SELECT 
    client_id,
    CASE 
        WHEN SUM(CASE WHEN Status = 'a' THEN 1 ELSE 0 END) > 0 
             AND SUM(CASE WHEN Status = 'd' THEN 1 ELSE 0 END) > 0 
        THEN 'Yes' 
        ELSE 'No' 
    END AS a_d_Flag
FROM client_details
GROUP BY client_id;

方案三：子查询+EXISTS判断 #

SELECT 
    DISTINCT c1.client_id,
    CASE 
        WHEN EXISTS (SELECT 1 FROM client_details c2 
                     WHERE c2.client_id = c1.client_id AND c2.Status = 'a')
             AND EXISTS (SELECT 1 FROM client_details c3 
                     WHERE c3.client_id = c1.client_id AND c3.Status = 'd')
        THEN 'Yes' 
        ELSE 'No' 
    END AS a_d_Flag
FROM client_details c1;

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