SQL查询：筛选所有关联IS_DELETED字段均为1的GLOBAL_ID

以下是几种适用于该场景的SQL实现方案（场景：表中存在重复GLOBAL_ID，且IS_DELETED字段值包含0和1）：

方法一：分组聚合筛选 #

通过GROUP BY对GLOBAL_ID分组，利用HAVING判断分组内IS_DELETED的最小值是否为1（因IS_DELETED仅含0和1，最小值为1则说明该分组所有记录的IS_DELETED都是1）：

SELECT GLOBAL_ID
FROM your_table_name
GROUP BY GLOBAL_ID
HAVING MIN(IS_DELETED) = 1;

方法二：排除法查询 #

先找出所有存在IS_DELETED=0记录的GLOBAL_ID，再筛选不在该集合中的GLOBAL_ID：

SELECT DISTINCT GLOBAL_ID
FROM your_table_name
WHERE GLOBAL_ID NOT IN (
    SELECT GLOBAL_ID
    FROM your_table_name
    WHERE IS_DELETED = 0
);

方法三：窗口函数统计 #

用窗口函数计算每个GLOBAL_ID对应的IS_DELETED=0记录数，筛选计数为0的GLOBAL_ID：

SELECT DISTINCT GLOBAL_ID
FROM (
    SELECT GLOBAL_ID,
           COUNT(CASE WHEN IS_DELETED = 0 THEN 1 END) OVER (PARTITION BY GLOBAL_ID) AS zero_count
    FROM your_table_name
) t
WHERE zero_count = 0;

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