Oracle中GROUP BY分组时COUNT(REFERENCE)为0不显示的问题

原SQL的问题在于WHERE条件直接过滤掉了ID为1269_1的所有行（因为该ID的REFERENCE列没有符合IN ('ADD', 'DIV')的值），导致分组后不存在对应分组，无法输出计数0的结果。要实现显示所有目标ID和目标REFERENCE组合的计数（包括0），可以通过构造目标数据集+左连接的方式实现：

修正后的SQL #

WITH TargetCombinations AS (
    -- 生成所有需要统计的ID和REFERENCE组合
    SELECT '6004_1' AS ID, 'ADD' AS REFERENCE UNION ALL
    SELECT '6004_1' AS ID, 'DIV' AS REFERENCE UNION ALL
    SELECT '1269_1' AS ID, 'ADD' AS REFERENCE UNION ALL
    SELECT '1269_1' AS ID, 'DIV' AS REFERENCE
)
SELECT 
    t.ID,
    t.REFERENCE,
    COUNT(t1.REFERENCE) AS reference_count
FROM TargetCombinations t
LEFT JOIN TABLE1 t1 
    ON t.ID = t1.ID 
    AND t1.REFERENCE = t.REFERENCE
GROUP BY t.ID, t.REFERENCE;

关键说明 #

1. 构造目标组合：通过CTE（TargetCombinations）生成所有需要统计的ID和REFERENCE配对，确保即使原表中没有对应数据，也能保留该组合。
2. 左连接关联：使用LEFT JOIN将目标组合与原表关联，这样即使原表中没有匹配的行，目标组合的记录也会被保留，此时t1.REFERENCE会为NULL。
3. 计数逻辑：COUNT(t1.REFERENCE)会忽略NULL值，因此没有匹配时计数结果为0，正好符合需求。
4. 移除多余条件：原SQL中的HAVING COUNT(REFERENCE) >= 0是多余的，因为COUNT结果不可能为负数，无需保留。

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