使用SQL GROUP BY与CASE WHEN实现特定行筛选需求

需求说明 #

按id分组时，优先保留category1字段中的AAA或BBB；当该id下仅存在ZZZ时，则保留ZZZ。

示例数据 #

create table #test1
( 
    id int, 
    category1 varchar(20)
);

insert into #test1 values (1,'AAA');
insert into #test1 values (1,'ZZZ');
insert into #test1 values (1,'YYY');
insert into #test1 values (2,'BBB');
insert into #test1 values (2,'ZZZ');
insert into #test1 values (3,'ZZZ');

目标结果 #

id   category1
-------------
 1   AAA
 2   BBB
 3   ZZZ

实现SQL #

SELECT 
    id,
    CASE
        -- 判断当前id下是否存在AAA或BBB
        WHEN MAX(CASE WHEN category1 IN ('AAA', 'BBB') THEN 1 ELSE 0 END) = 1
        -- 存在则取出对应的AAA/BBB值
        THEN MAX(CASE WHEN category1 IN ('AAA', 'BBB') THEN category1 END)
        -- 不存在则保留唯一的ZZZ
        ELSE MAX(category1)
    END AS category1
FROM #test1
GROUP BY id;

逻辑解释 #

1. 内层第一个CASE：对每行数据标记，若category1是AAA或BBB则返回1，否则返回0；通过MAX聚合判断当前id下是否存在目标值。
2. 外层CASE根据判断结果分支处理：
   • 存在目标值时，用第二个CASE筛选出AAA/BBB，再通过MAX取出（每个id下最多一个这类值，MAX不影响结果）。
   • 不存在目标值时，直接用MAX取出当前id下的category1（此时仅存在ZZZ）。

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