如何编写高效SQL查询筛选从未关联Status为Z的ID对应所有行

以下是几种高效实现需求的SQL写法，可根据你的使用场景选择：

方案1：NOT EXISTS写法（性能最优，推荐使用） #

大多数数据库的优化器对这种关联子查询的优化效果最好，只要ID和Status列建立了联合索引，执行效率极高，且不受ID是否为NULL的影响。

SELECT ID, Status
FROM T t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM T t2 
    WHERE t2.ID = t1.ID 
      AND t2.Status = 'Z'
);

方案2：NOT IN写法（最直观易懂） #

写法简单易读，仅需注意如果表中ID允许为NULL，不要使用该写法，会导致查询结果为空。

SELECT ID, Status
FROM T
WHERE ID NOT IN (
    SELECT DISTINCT ID 
    FROM T 
    WHERE Status = 'Z'
);

方案3：聚合+关联写法（适合需要同步统计其他指标的场景） #

如果查询时需要同时对ID做其他聚合统计，可以用该写法：

SELECT t1.ID, t1.Status
FROM T t1
INNER JOIN (
    SELECT ID
    FROM T
    GROUP BY ID
    HAVING SUM(CASE WHEN Status = 'Z' THEN 1 ELSE 0 END) = 0
) t2 ON t1.ID = t2.ID;

如果你使用的是支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server等），还可以简化为：

SELECT ID, Status
FROM (
    SELECT 
        ID,
        Status,
        SUM(CASE WHEN Status = 'Z' THEN 1 ELSE 0 END) OVER(PARTITION BY ID) AS z_cnt
    FROM T
) tmp
WHERE z_cnt = 0;

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