如何筛选存在混合EMP值的ID对应的非9999记录？SQL查询求助

原始数据表（table_A） #

ID  EMP
1   9999
1   1
2   9999
2   2
2   3
3   9999
3   9999
3   4
3   4
3   4
4   9999
4   9999
4   9999
5   5
5   6

需求说明 #

筛选出EMP≠9999的记录，但对应的ID必须同时存在EMP=9999和非9999的值，排除仅含9999的ID4、仅含非9999的ID5。

预期输出 #

id emp
1   1
2   2
2   3
3   4
3   4
3   4

原SQL的问题 #

原SQL的子查询仅筛选了存在非9999值的ID，但未检查该ID是否同时存在9999值，导致ID5（仅含非9999值）的记录被错误包含，不符合需求。

正确SQL写法 #

写法一：GROUP BY + HAVING 筛选符合条件的ID #

SELECT ID, EMP
FROM table_a
WHERE EMP <> 9999
AND ID IN (
    SELECT ID
    FROM table_a
    GROUP BY ID
    HAVING MAX(CASE WHEN EMP = 9999 THEN 1 ELSE 0 END) = 1
    AND MAX(CASE WHEN EMP <> 9999 THEN 1 ELSE 0 END) = 1
)

逻辑说明：

• 子查询通过GROUP BY ID分组后，用两个MAX(CASE...)分别判断该ID是否存在9999值、是否存在非9999值，只有两个条件都满足的ID才会被选中。
• 外层查询筛选出这些ID对应的非9999记录。

写法二：EXISTS 关联查询（性能更优） #

SELECT t1.ID, t1.EMP
FROM table_a t1
WHERE t1.EMP <> 9999
AND EXISTS (
    SELECT 1 FROM table_a t2 WHERE t2.ID = t1.ID AND t2.EMP = 9999
)

逻辑说明：

• 外层查询先筛选非9999的记录，再通过EXISTS检查当前ID是否存在9999的记录，直接排除掉仅含非9999值的ID（如ID5）和仅含9999的ID（如ID4）。

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