快捷導(dǎo)航

MySql的分組函數(shù) ROLLUP 語法及完美解決方案

更新時間：2026年03月03日 10:30:09 作者：xixi_666

這篇文章總結(jié)了在MySQL中如何使用`GROUP BY`和`ROLLUP`來實(shí)現(xiàn)分組匯總,通過`WITH ROLLUP`和`GROUPING()`函數(shù),可以靈活地控制匯總級別,并使用`HAVING`子句來過濾掉中間匯總行,只保留明細(xì)和最終總計行,感興趣的朋友跟隨小編一起看看吧

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 (
    -- 模擬你的數(shù)據(jù)表
    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)” 就只列出明細(xì)和總計行

解決方案3：如果只需要明細(xì)和總計（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)  -- 明細(xì)行
    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