如何筛选所有关联记录delete_flag均为1的primary_id

解法一：GROUP BY + HAVING 统计极值 #

利用MIN()函数判断该primary_id下所有delete_flag的最小值是否为1——只要有一条记录delete_flag为0，最小值就会是0，只有全为1时最小值才等于1：

SELECT primary_id
FROM your_table
GROUP BY primary_id
HAVING MIN(delete_flag) = 1;

解法二：GROUP BY + HAVING 统计不符合条件的记录数 #

通过统计delete_flag不等于1的记录数量，当数量为0时，说明该primary_id下所有记录的delete_flag都是1：

SELECT primary_id
FROM your_table
GROUP BY primary_id
HAVING SUM(CASE WHEN delete_flag != 1 THEN 1 ELSE 0 END) = 0;

解法三：子查询排除法 #

先找出所有存在delete_flag=0的primary_id，再从总列表中排除这些id，剩下的就是所有关联记录delete_flag全为1的primary_id：

SELECT DISTINCT primary_id
FROM your_table
WHERE primary_id NOT IN (
    SELECT primary_id
    FROM your_table
    WHERE delete_flag = 0
);

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