SQL Server ROLLUP 群組彙總完整教學：小計、總計與 GROUPING() 識別彙總列

SQL Server GROUP BY ... WITH ROLLUP 可在單一查詢中同時產生明細列、各層級小計與總計。本文以省市分數統計為例，完整示範單層/多層 ROLLUP、GROUPING() 識別彙總列、搭配 CASE 美化輸出、以及多欄位分組的彙總階層邏輯。

測試資料

CREATE TABLE tb (province NVARCHAR(10), city NVARCHAR(10), score INT);
INSERT tb VALUES
(N'陝西', N'西安', 3), (N'陝西', N'安康', 4), (N'陝西', N'漢中', 2),
(N'廣東', N'廣州', 5), (N'廣東', N'珠海', 2), (N'廣東', N'東莞', 3),
(N'江蘇', N'南京', 6), (N'江蘇', N'蘇州', 1);

1. 單層分組 + ROLLUP (省份總分 + 總計)

SELECT province AS 省, SUM(score) AS 分數
FROM tb
GROUP BY province WITH ROLLUP;

結果

省     分數
廣東   10
江蘇   7
陝西   9
NULL   26  -- 總計列

美化輸出：GROUPING() 識別彙總列

SELECT 
    CASE WHEN GROUPING(province) = 1 THEN N'合計' ELSE province END AS 省,
    SUM(score) AS 分數
FROM tb
GROUP BY province WITH ROLLUP;

結果

省     分數
廣東   10
江蘇   7
陝西   9
合計   26

2. 兩層分組 + ROLLUP (省份 → 城市 → 省份小計 → 總計)

SELECT province AS 省, city AS 市, SUM(score) AS 分數
FROM tb
GROUP BY province, city WITH ROLLUP;

結果 (NULL 代表該層彙總)

省     市     分數
廣東   東莞   3
廣東   廣州   5
廣東   珠海   2
廣東   NULL   10   -- 廣東省小計
江蘇   南京   6
江蘇   蘇州   1
江蘇   NULL   7    -- 江蘇省小計
陝西   安康   4
陝西   漢中   2
陝西   西安   3
陝西   NULL   9    -- 陝西省小計
NULL   NULL   26   -- 總計

GROUPING() 多欄位識別：區分城市小計、省份小計、總計

SELECT 
    province, city, SUM(score) AS 分數,
    GROUPING(province) AS gp,  -- 1=省份層彙總(含總計)
    GROUPING(city) AS gc       -- 1=城市層彙總
FROM tb
GROUP BY province, city WITH ROLLUP;

結果

省     市     分數 gp gc
廣東   東莞   3    0  0
廣東   廣州   5    0  0
廣東   珠海   2    0  0
廣東   NULL   10   0  1  -- 城市小計 (gp=0, gc=1)
江蘇   南京   6    0  0
江蘇   蘇州   1    0  0
江蘇   NULL   7    0  1  -- 城市小計
陝西   安康   4    0  0
陝西   漢中   2    0  0
陝西   西安   3    0  0
陝西   NULL   9    0  1  -- 城市小計
NULL   NULL   26   1  1  -- 總計 (gp=1, gc=1)

完整美化輸出

SELECT 
    CASE WHEN GROUPING(province) = 1 THEN N'合計' ELSE province END AS 省,
    CASE 
        WHEN GROUPING(city) = 1 AND GROUPING(province) = 0 THEN N'小計'
        WHEN GROUPING(city) = 1 AND GROUPING(province) = 1 THEN N''
        ELSE city 
    END AS 市,
    SUM(score) AS 分數
FROM tb
GROUP BY province, city WITH ROLLUP;

結果

省     市     分數
廣東   東莞   3
廣東   廣州   5
廣東   珠海   2
廣東   小計   10
江蘇   南京   6
江蘇   蘇州   1
江蘇   小計   7
陝西   安康   4
陝西   漢中   2
陝西   西安   3
陝西   小計   9
合計         26

ROLLUP 階層邏輯解析

GROUP BY A, B, C WITH ROLLUP 產生層級：

1. 明細：GROUP BY A, B, C
2. C 小計：GROUP BY A, B
3. B 小計：GROUP BY A
4. 總計：GROUP BY () (空分組)

順序敏感：GROUP BY province, city ≠ GROUP BY city, province，前者「省份→城市」，後者「城市→省份」。

GROUPING_ID() 替代多欄位 GROUPING()

SELECT 
    province, city, SUM(score) AS 分數,
    GROUPING_ID(province, city) AS gid  -- 位元組合：province=bit1, city=bit0
FROM tb
GROUP BY province, city WITH ROLLUP;

gid 對照

gid (二進位)  意義
0 (00)        明細列
1 (01)        城市小計
3 (11)        總計
-- 注意：省份小計在兩層 ROLLUP 中不會單獨出現 (需三層以上)

CUBE vs ROLLUP 差異

效能與限制

• ROLLUP 結果列數 = 明細列數 + 階層數，大量分組可能產生大量彙總列
• 搭配索引 (province, city) 可避免 Sort
• SQL 2008+ 支援 GROUPING SETS 自訂彙總組合 (比 ROLLUP/CUBE 更彈性)
• GROUPING() 僅能用於 SELECT、HAVING、ORDER BY 子句

適用版本

• SQL Server 2000+ (ROLLUP)
• SQL Server 2008+ (GROUPING SETS)
• 標準 SQL:1999 支援