基于双列聚合的SQL查询：按客户统计状态及房间标记

原始数据表 client_details #

client_id   Status      room
   1         a           XS
   1         b           XS
   1         d           XS
   1         e           L
   2         a           M
   2         d           M
   3         a           XS
   3         b           XS
   3         e           L
   4         a           L
   4         d           L
   5         b           XS

需求描述 #

按client_id分组，生成两个标记字段：

• a_d_Flag：客户同时存在Status为'a'和'd'的记录时标记为'Yes'，否则为'No'
• Xs_flag：客户曾入住room为'XS'的房间时标记为'Yes'，否则为'No'

期望结果集 #

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

原查询的问题 #

原查询存在两个核心错误：

1. GROUP BY子句包含了room，导致结果会按「客户+房间」分组，无法得到每个客户唯一的一行记录
2. xs_flag的判断未使用聚合逻辑，直接用room = 'XS'只能判断当前分组的房间，无法覆盖客户的所有入住记录

修正后的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,
    CASE 
        WHEN MAX(CASE WHEN room = 'XS' THEN 1 ELSE 0 END) = 1 
        THEN 'Yes' 
        ELSE 'No' 
    END AS Xs_flag
FROM client_details
GROUP BY client_id;

修正说明 #

• 移除GROUP BY中的room，确保每个client_id仅返回一行结果
• 针对Xs_flag，用MAX()聚合函数判断客户是否存在任意一条room = 'XS'的记录：存在时对应CASE结果为1，MAX取到1则标记'Yes'，否则为'No'
• a_d_Flag的原逻辑是正确的，通过COUNT(DISTINCT...)统计是否同时存在'a'和'd'两种状态，保留该逻辑即可

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