Oracle SQL查询无返回记录时显示0的实现问题咨询

你的问题核心是：第二个子查询因为没有符合HAVING COUNT(DISTINCT ID)>1的记录，直接返回空结果，导致UNION ALL后没有'U'相关的行，外层GROUP BY自然无法统计到'U'。NVL在这里失效，因为它只能处理NULL值，而不是完全没有行的情况。

下面是两种简洁的解决方案：

方案一：子查询嵌套+NVL #

直接针对两个统计需求分别处理，对于'U'的统计，用子查询获取符合条件的CAT数量，无结果时用NVL转0：

SELECT 
    'T' AS TOTAL_CNT,
    COUNT(DISTINCT cat) AS COUNT(*)
FROM temp
UNION ALL
SELECT 
    'U' AS TOTAL_CNT,
    NVL(
        (SELECT COUNT(*) FROM (SELECT 1 FROM temp GROUP BY cat HAVING COUNT(DISTINCT id) > 1)),
        0
    ) AS COUNT(*)
FROM dual;

方案二：固定行左连接 #

先生成包含'T'和'U'的固定行集合，再左连接统计结果，确保即使无匹配数据也能显示0：

WITH fixed_types AS (
    SELECT 'T' AS cnt_type FROM dual
    UNION ALL
    SELECT 'U' AS cnt_type FROM dual
),
stat_data AS (
    SELECT 'T' AS cnt_type, COUNT(DISTINCT cat) AS cnt FROM temp
    UNION ALL
    SELECT 'U' AS cnt_type, COUNT(*) AS cnt 
    FROM (SELECT 1 FROM temp GROUP BY cat HAVING COUNT(DISTINCT id) > 1)
)
SELECT 
    ft.cnt_type AS TOTAL_CNT,
    NVL(sd.cnt, 0) AS COUNT(*)
FROM fixed_types ft
LEFT JOIN stat_data sd ON ft.cnt_type = sd.cnt_type;

两种方案都能得到你想要的输出：

TOTAL_CNT    COUNT(*)
T            6
U            0

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