SQL 實現分組小計、合計與排序：GROUPING + ROLLUP 進階技巧

核心技術：GROUPING SETS / ROLLUP + GROUPING() 函數

利用 GROUP BY ... WITH ROLLUP 生成聚合行，配合 GROUPING(column) 區分明細行（0）與匯總行（1），實現單查詢輸出明細+小計+總計。

完整實戰代碼

-- 測試數據
CREATE TABLE #TB(ID VARCHAR(10), BANK_ID VARCHAR(10), OP_DATE VARCHAR(10), OPERATOR_NO VARCHAR(20), AMT DECIMAL(10,2));
INSERT #TB SELECT '1111','001','20121210',1234567,111.00 UNION ALL
SELECT '2222','002','20121210',1234567,222.00 UNION ALL
SELECT '3333','001','20121112',1234567,250.00 UNION ALL
SELECT '4444','002','20121110',1234567,330.00 UNION ALL
SELECT '5555','001','20121210',1234567,300.00 UNION ALL
SELECT '6666','002','20121112',1234567,150.00;

-- 查詢：分組BANK_ID+OP_DATE升序，顯示小計合計
SELECT A.ID, A.BANK_ID, A.AMT, B.OP_DATE, B.OPERATOR_NO
FROM (
    -- 統計部分：ROLLUP產生小計/合計行
    SELECT 
        ID = CASE 
            WHEN GROUPING(BANK_ID) = 1 THEN '合計'
            WHEN GROUPING(ID) = 1 THEN '小計'
            ELSE ID END,
        BANK_ID,
        SUM(AMT) AS AMT,
        ORDER_SIGN1 = GROUPING(BANK_ID),  -- 合計排最後
        ORDER_SIGN2 = BANK_ID,             -- 按銀行分組
        ORDER_SIGN3 = GROUPING(ID)         -- 小計在組內最後
    FROM #TB
    GROUP BY BANK_ID, ID WITH ROLLUP
    HAVING GROUPING(ID) = 1  -- 只保留小計(GROUPING(ID)=1)和合計(GROUPING(BANK_ID)=1)

    UNION ALL

    -- 明細部分
    SELECT 
        ID, BANK_ID, AMT,
        ORDER_SIGN1 = 0, ORDER_SIGN2 = BANK_ID, ORDER_SIGN3 = 0
    FROM #TB
) A
LEFT JOIN (
    SELECT ID, OP_DATE, OPERATOR_NO FROM #TB
) B ON A.ID = B.ID
ORDER BY ORDER_SIGN1, ORDER_SIGN2, ORDER_SIGN3, OP_DATE;

-- 測試結果
/* 
ID    BANK_ID AMT     OP_DATE   OPERATOR_NO
3333  001     250.00  20121112  1234567
5555  001     300.00  20121210  1234567
1111  001     111.00  20121210  1234567
小計  001     661.00  NULL      NULL
4444  002     330.00  20121110  1234567
6666  002     150.00  20121112  1234567
2222  002     222.00  20121210  1234567
小計  002     702.00  NULL      NULL
合計  NULL    1363.00 NULL      NULL
*/

DROP TABLE #TB;

關鍵點深度解析

1. GROUPING() 函數

• 返回 1 表示該列在當前行是聚合生成的（NULL 由 ROLLUP 填充）
• 返回 0 表示該列保留原始值
• 用於 CASE 表達式生成「小計/合計」標籤

2. ORDER_SIGN 排序欄位設計

3. HAVING GROUPING(ID) = 1

關鍵過濾：ROLLUP 會產生 (BANK_ID, ID)、(BANK_ID, NULL)、(NULL, NULL) 三層。GROUPING(ID)=1 精確保留後兩層（小計+合計），排除明細行重複。

4. LEFT JOIN 回原表

聚合行丟失非聚合列（OP_DATE, OPERATOR_NO），通過 ID 關聯回原表補全明細行信息，聚合行自然為 NULL。

遷移到 GROUPING SETS (SQL 2008+)

GROUP BY GROUPING SETS (
    (BANK_ID, ID),      -- 明細
    (BANK_ID),          -- 小計
    ()                  -- 總計
)

語義更清晰，性能相當，推薦新版本使用。