如何在Oracle SQL中使用COUNT(DISTINCT)筛选CA_ID和SA_ID同但BA_ID异的行

需求是找出所有满足「CA_ID与SA_ID组合相同，但对应的BA_ID存在多个不同值」的原始记录，先看示例数据和期望结果：

示例数据 #

CA_ID   BA_ID   SA_ID       
----    -----   --------    
CA1     BA1     SA1 
CA1     BA2     SA1 
CA1     BA2     SA2
CA1     BA3     SA1 
CA2     BA4     SA3
CA2     BA4     SA4
CA2     BA5     SA4
CA3     BA6     SA6

期望查询结果 #

CA_ID   BA_ID   SA_ID       
----    -----   --------    
CA1     BA1     SA1 
CA1     BA2     SA1 
CA1     BA3     SA1 
CA2     BA4     SA4
CA2     BA5     SA4

解决方案 #

方法一：分组子查询+关联（符合你提到的HAVING COUNT(DISTINCT)需求） #

先通过分组筛选出符合条件的CA_ID+SA_ID组合，再关联原表获取完整记录：

SELECT t.CA_ID, t.BA_ID, t.SA_ID
FROM your_table t
JOIN (
    -- 筛选出同一CA+SA下有多个不同BA_ID的组合
    SELECT CA_ID, SA_ID
    FROM your_table
    GROUP BY CA_ID, SA_ID
    HAVING COUNT(DISTINCT BA_ID) > 1
) valid_groups ON t.CA_ID = valid_groups.CA_ID 
               AND t.SA_ID = valid_groups.SA_ID
ORDER BY t.CA_ID, t.SA_ID, t.BA_ID;

方法二：窗口函数（性能更优） #

如果数据量较大，窗口函数可以避免额外关联，直接计算每个分组的BA_ID数量：

SELECT CA_ID, BA_ID, SA_ID
FROM (
    SELECT 
        CA_ID, BA_ID, SA_ID,
        -- 计算当前CA+SA分组下不同BA_ID的数量
        COUNT(DISTINCT BA_ID) OVER (PARTITION BY CA_ID, SA_ID) AS ba_distinct_count
    FROM your_table
)
WHERE ba_distinct_count > 1
ORDER BY CA_ID, SA_ID, BA_ID;

两种方法都能得到期望的结果，记得把your_table替换成你的实际表名。

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