如何编写SQL查询仅返回全为0值的记录？现有查询失效求助

原始数据表X #

+--------+-----+
|   Id   | set |
+--------+-----+
| ABC123 |   1 |
| ABC123 |   0 |
| ABC123 |   1 |
| XYZ123 |   0 |
| XYZ123 |   0 |
| XYZ123 |   0 |
| ZAC123 |   1 |
| ZAC123 |   0 |
| TNB123 |   0 |
| TNB123 |   0 |
| TNB123 |   0 |
| BBB123 |   1 |
| BBB123 |   0 |
| BBB123 |   1 |
| BBB123 |   1 |
+--------+-----+

目标结果 #

需要筛选出所有set值均为0的ID对应的全部行，结果如下：

+--------+-----+
|   Id   | set |
+--------+-----+
| XYZ123 |   0 |
| XYZ123 |   0 |
| XYZ123 |   0 |
| TNB123 |   0 |
| TNB123 |   0 |
| TNB123 |   0 |
+--------+-----+

原查询的问题 #

原SQL语句：

Select * 
from X
where set='0'
having count(*)>1 

这个查询无法得到正确结果的原因：

• HAVING子句必须配合GROUP BY使用才能按分组筛选，单独使用时会将整个结果集视为单一分组，无法针对每个ID做判断。
• 原逻辑仅筛选了set=0的行，但没有排除那些同时存在set=1记录的ID——比如ABC123虽然有set=0的行，但也有set=1的，显然不符合目标要求。

正确的SQL写法 #

方法1：子查询筛选符合条件的ID #

先找出所有没有set=1的ID，再关联原表获取这些ID的全部行：

SELECT x.*
FROM X x
WHERE x.Id IN (
    SELECT Id
    FROM X
    GROUP BY Id
    HAVING MAX(set) = 0 -- 因为set只有0和1，MAX为0说明所有行都是0
)

方法2：使用NOT EXISTS排除不符合的ID #

SELECT x.*
FROM X x
WHERE NOT EXISTS (
    SELECT 1
    FROM X y
    WHERE y.Id = x.Id
    AND y.set = 1
)

方法3：窗口函数（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

SELECT Id, set
FROM (
    SELECT 
        Id, 
        set,
        MAX(set) OVER (PARTITION BY Id) AS max_set
    FROM X
) t
WHERE max_set = 0

以上方法都能准确筛选出所有set值全为0的ID对应的所有行，得到目标结果。

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