使用ROLLUP在查询结果最后一行添加汇总数据

表结构与数据 #

create table t(mon number, id number);

insert into t(mon, id)   
SELECT 4,   1006   FROM DUAL UNION ALL
SELECT 5,   10618  FROM DUAL UNION ALL
SELECT 2,   9999   FROM DUAL UNION ALL
SELECT 2,   9999   FROM DUAL UNION ALL
SELECT 2,   1000   FROM DUAL;

原查询问题 #

原查询试图通过ROLLUP(id)生成按id分组的月度计数及汇总行，但逻辑错误导致汇总结果不符合预期。原查询代码：

select id, 
    coalesce(case when max(mon) = 1 then count(*) end, 0) Jan,
    coalesce(case when max(mon) = 2 then count(*) end, 0) Feb, 
    coalesce(case when max(mon) = 3 then count(*) end, 0) Mar,
    coalesce(case when max(mon) = 4 then count(*) end, 0) Apr,
    coalesce(case when max(mon) = 5 then count(*) end, 0) May,
    coalesce(case when max(mon) = 6 then count(*) end, 0) Jun, 
    count(*) from t group by rollup(id);

错误核心：max(mon) = x仅当当前分组（或汇总）中所有mon值等于x时才成立。汇总行中max(mon)取全表最大值5，因此只有May列有值，其他月度列均为0，无法统计各月总条数。

修正后的查询 #

改用SUM(CASE...)统计每个月的记录数，确保分组和汇总时都能正确累加对应月份的条数：

select 
    nvl(id, 0) as id,
    sum(case when mon = 1 then 1 else 0 end) as Jan,
    sum(case when mon = 2 then 1 else 0 end) as Feb,
    sum(case when mon = 3 then 1 else 0 end) as Mar,
    sum(case when mon = 4 then 1 else 0 end) as Apr,
    sum(case when mon = 5 then 1 else 0 end) as May,
    sum(case when mon = 6 then 1 else 0 end) as Jun,
    count(*) as total
from t 
group by rollup(id);

执行结果 #

ID    JAN FEB MAR APR MAY JUN TOTAL
1006  0   0   0   1   0   0   1
10618 0   0   0   0   1   0   1
9999  0   2   0   0   0   0   2
1000  0   1   0   0   0   0   1
0     0   3   0   1   1   0   5

（注：原期望的汇总行数值存在笔误，上述结果为实际数据的正确统计）

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