MySql的分组函数 ROLLUP 语法及完美解决方案_Mysql

oracle: group by rollup(字段a)
mysql: group by 字段a with rollup

解决方案1：使用 with rollup和 grouping()

select 
    case 
        when grouping(c_jxdm) = 1 then 2
        else 0 
    end as n_grouping,
    -- grouping(c_jxdm) c_jxdm_grouping,
    -- grouping(c_pxlb) c_pxlb_grouping,
    coalesce(c_jxdm, '合计') as c_jxdm,
    coalesce(c_pxlb, '') as c_pxlb,
    count(*) as n_cnt
from (
    -- 模拟你的数据表
    select 'name1' as name, '103001' as c_jxdm, '1' as c_pxlb
    union all
    select 'name2', '103001', '1'
    union all
    select 'name3', '103001', '2'
    union all
    select 'name4', '103001', '3'
    union all
    select 'name5', '103001', '3'
    union all
    select 'name6', '112001', '1'
    union all
    select 'name7', '112001', '1'
    union all
    select 'name8', '138001', '2'
) as t
group by c_jxdm, c_pxlb with rollup
having grouping(c_pxlb) = 0 or (grouping(c_jxdm) = 1 and grouping(c_pxlb) = 1)
order by 
    grouping(c_jxdm) asc,
    c_jxdm,
    grouping(c_pxlb) asc,
    c_pxlb;

n_grouping	c_jxdm	c_pxlb	n_cnt
0	103001	1	2
0	103001	2	1
0	103001	3	2
0	112001	1	2
0	138001	2	1
2	合计		8

解决方案2：更简洁的写法（mysql 8.0+）

with sample_data as (
    select 'name1' as name, '103001' as c_jxdm, '1' as c_pxlb
    union all select 'name2', '103001', '1'
    union all select 'name3', '103001', '2'
    union all select 'name4', '103001', '3'
    union all select 'name5', '103001', '3'
    union all select 'name6', '112001', '1'
    union all select 'name7', '112001', '1'
    union all select 'name8', '138001', '2'
)
select 
    case 
        when grouping(c_jxdm) = 1 then 2
        when grouping(c_pxlb) = 0 then 0
        else 1
    end as n_grouping,
    -- grouping(c_jxdm) c_jxdm_grouping,
    -- grouping(c_pxlb) c_pxlb_grouping,
    coalesce(c_jxdm, '合计') as c_jxdm,
    coalesce(c_pxlb, '') as c_pxlb,
    count(*) as n_cnt
from sample_data
group by c_jxdm, c_pxlb with rollup
-- having grouping(c_pxlb) = 0 or (grouping(c_jxdm) = 1 and grouping(c_pxlb) = 1)
order by 
    grouping(c_jxdm),
    c_jxdm,
    grouping(c_pxlb),
    c_pxlb;

n_grouping	c_jxdm	c_pxlb	n_cnt
0	103001	1	2
0	103001	2	1
0	103001	3	2
1	103001		5
0	112001	1	2
1	112001		2
0	138001	2	1
1	138001		1
2	合计		8

加上“having grouping(c_pxlb) = 0 or (grouping(c_jxdm) = 1 and grouping(c_pxlb) = 1)” 就只列出明细和总计行

解决方案3：如果只需要明细和总计（2级汇总）

with sample_data as (
    select 'name1' as name, '103001' as c_jxdm, '1' as c_pxlb
    union all select 'name2', '103001', '1'
    union all select 'name3', '103001', '2'
    union all select 'name4', '103001', '3'
    union all select 'name5', '103001', '3'
    union all select 'name6', '112001', '1'
    union all select 'name7', '112001', '1'
    union all select 'name8', '138001', '2'
)
select 
    grouping(c_jxdm) + grouping(c_pxlb) as n_grouping,
    ifnull(c_jxdm, '合计') as c_jxdm,
    ifnull(c_pxlb, '') as c_pxlb,
    count(*) as n_cnt
from sample_data
group by c_jxdm, c_pxlb with rollup
having (grouping(c_jxdm) = 0 and grouping(c_pxlb) = 0)  -- 明细行
    or (grouping(c_jxdm) = 1 and grouping(c_pxlb) = 1)  -- 总计行
order by grouping(c_jxdm), c_jxdm, grouping(c_pxlb), c_pxlb;
n_grouping	c_jxdm	c_pxlb	n_cnt
0	103001	1	2
0	103001	2	1
0	103001	3	2
0	112001	1	2
0	138001	2	1
2	合计		8