Oracle SQL带默认值的聚合统计：按ID统计YES/NO状态数量

原始表结构与示例数据 #

待统计表counterTb的字段与示例数据如下：

id  taxPin  Status
1   123     Y
1   234     Y
1   345     Y
1   456     N
1   567     N
2   678     Y
2   789     Y
2   890     null
2   901     N
1   100     null

统计规则要求 #

• 状态映射：表中Status字段值为Y映射为YES、为N映射为NO
• 统计口径：仅统计指定ID下的有效状态记录，Status为null的记录不计入总数
• 输出固定结构：
  • 当指定ID存在匹配数据时，返回对应状态的真实统计值（以ID=1的查询结果为例）：

    id Status   count
    1   YES     3
    1   NO      2
    

  • 当指定ID在表中无任何匹配记录时（如查询ID=3），仍需返回YES、NO两行固定结果，对应统计值默认置为0：

    id Status    count
    3   YES      0
    3   NO       0
    

• 实现约束：必须使用Oracle原生聚合函数完成逻辑，无匹配数据的场景不能返回空结果集。

参考实现SQL #

-- 执行时替换:target_id为需要查询的目标ID值
WITH base_row AS (
    -- 构造固定返回的两行状态骨架，保证无数据时也能输出完整结构
    SELECT :target_id AS id, 'YES' AS status FROM dual
    UNION ALL
    SELECT :target_id AS id, 'NO' AS status FROM dual
),
real_stat AS (
    -- 聚合指定ID下的有效状态计数，自动过滤null状态值
    SELECT
        id,
        DECODE(status, 'Y', 'YES', 'N', 'NO') AS status,
        COUNT(taxPin) AS count
    FROM counterTb
    WHERE id = :target_id
      AND status IN ('Y', 'N')
    GROUP BY id, DECODE(status, 'Y', 'YES', 'N', 'NO')
)
SELECT
    b.id,
    b.status,
    -- 左关联缺失的统计行用0补全
    NVL(r.count, 0) AS count
FROM base_row b
LEFT JOIN real_stat r
  ON b.id = r.id
 AND b.status = r.status
ORDER BY b.status DESC;

逻辑说明 #

• 利用Oracle的dual虚表构造固定的结果骨架，从根源上避免“目标ID无匹配数据时返回空集”的问题
• 聚合阶段直接在WHERE条件中限定status为Y/N，自动剔除null值的无效记录，统计口径完全符合要求
• 采用左连接关联预构造的骨架行和实际聚合结果，通过NVL函数将无匹配的空值替换为默认0值，完全匹配输出格式要求
• 全流程使用Oracle原生的COUNT聚合函数、DECODE/NVL内置函数，无跨数据库的兼容语法问题。

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