SQL如何排除同组COL2存在非指定列表值的COL1查询结果

现有测试数据表结构及数据如下：

当前执行的查询SQL：

select COL1 
from table t 
where t.COL3= 'X' 
  and t.COL2 in ('L1', 'L2', 'L3', 'L4');

实际返回结果：

U1
U2
U3
U4

期望返回结果：

U2
U3

过滤规则：同COL1对应的COL3='X'的记录组中，只要存在任意一条记录的COL2取值不在指定IN列表范围内，就将该COL1整体过滤。

该需求可以通过单条SQL实现，以下提供两种主流通用写法，兼容绝大多数关系型数据库：

写法1：GROUP BY + HAVING 聚合判断（推荐，性能更优） #

SELECT COL1
FROM t
WHERE COL3 = 'X'
GROUP BY COL1
HAVING SUM(CASE WHEN COL2 NOT IN ('L1', 'L2', 'L3', 'L4') THEN 1 ELSE 0 END) = 0;

逻辑说明：

• 先筛选所有COL3='X'的记录，按COL1分组
• 逐组统计COL2不在目标列表内的记录条数，仅保留条数为0的分组（即组内所有COL2都在指定列表中）
• 该写法会自动过滤U1、U4这类存在列表外COL2值的分组，也会自动排除U5这类无任何COL2匹配列表的分组

写法2：NOT EXISTS 反向排除 #

SELECT DISTINCT t1.COL1
FROM t t1
WHERE t1.COL3 = 'X'
  AND t1.COL2 IN ('L1', 'L2', 'L3', 'L4')
  AND NOT EXISTS (
      SELECT 1
      FROM t t2
      WHERE t2.COL1 = t1.COL1
        AND t2.COL3 = 'X'
        AND t2.COL2 NOT IN ('L1', 'L2', 'L3', 'L4')
  );

逻辑说明：

• 先匹配出COL3='X'且COL2在目标列表的COL1候选集
• 再通过NOT EXISTS排除掉候选集中，存在同COL1、COL3='X'但COL2不在列表内的COL1值

以上两种写法执行后，均可得到期望的U2、U3结果。

内容的提问来源于stack exchange，提问作者Zubair P.