使用GROUP BY与HAVING COUNT(*) >1筛选字段的查询问题

先来说说为什么你的当前查询漏掉了5 5：你的查询里，第一个UNION分支要求CUST_ID = REF_ID且REF_ID NOT IN (那些出现次数>1的REF_ID)，但REF_ID=5的出现次数是3次，所以5 5被这个分支过滤掉了；第二个分支要求CUST_ID != REF_ID，显然也不匹配5 5，所以这条记录就被排除了。

你的目标是返回以下两类记录：

• CUST_ID等于REF_ID的所有记录（不管这个REF_ID出现多少次）
• CUST_ID不等于REF_ID，但REF_ID只出现一次的记录

基于这个逻辑，我提供两种简洁的实现方案：

方案1：使用窗口函数（推荐，更简洁） #

窗口函数可以直接在每条记录上计算对应REF_ID的出现次数，不需要额外的JOIN操作：

SELECT CUST_ID, REF_ID
FROM (
    SELECT 
        CUST_ID, 
        REF_ID,
        -- 计算当前REF_ID在整张表中的出现次数
        COUNT(*) OVER (PARTITION BY REF_ID) AS ref_count
    FROM CUST_REF
) sub_query
-- 筛选条件：要么CUST和REF相等，要么REF只出现一次
WHERE CUST_ID = REF_ID OR ref_count = 1;

方案2：使用关联子查询 #

先通过子查询统计每个REF_ID的出现次数，再关联原表筛选：

SELECT cr.CUST_ID, cr.REF_ID
FROM CUST_REF cr
JOIN (
    SELECT REF_ID, COUNT(*) AS ref_count
    FROM CUST_REF
    GROUP BY REF_ID
) ref_counts ON cr.REF_ID = ref_counts.REF_ID
WHERE cr.CUST_ID = cr.REF_ID OR ref_counts.ref_count = 1;

这两种方案都会返回你想要的结果：1 7、2 2、5 5，同时排除掉3 5和4 5。

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