WITH ROLLUP 核心價值

GROUP BY ... WITH ROLLUP 在單一查詢中產生多層級彙總（小計、總計），是 OLAP 分析與報表產生的利器。它自動在分組層級間插入超級聚合行，以 NULL 標識彙總層級。

語法與執行邏輯

SELECT 分組欄位1, 分組欄位2, ..., 聚合函數(數值欄位)
FROM 資料表
GROUP BY 分組欄位1, 分組欄位2, ... WITH ROLLUP;

產生層級（以 GROUP BY A, B, C 為例）：

1. 最細層：A, B, C 的每種組合
2. 第二層：A, B 的每種組合（C = NULL，表示 B 群組小計）
3. 第三層：A 的每種值（B = NULL, C = NULL，表示 A 群組小計）
4. 總計層：A = NULL, B = NULL, C = NULL（整體總計）

實戰範例：銷售資料多維分析

建表與測試資料

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit INT
);

INSERT INTO sales (year, country, product, profit) VALUES
(2000, 'Finland', 'Computer', 1500),
(2000, 'Finland', 'Phone', 100),
(2000, 'India', 'Calculator', 150),
(2000, 'USA', 'Computer', 2000),
(2001, 'Finland', 'Phone', 200),
(2001, 'India', 'Calculator', 300),
(2001, 'USA', 'Computer', 2500);

1. 單層分組 + ROLLUP

SELECT year, SUM(profit) AS total_profit
FROM sales
GROUP BY year WITH ROLLUP;

結果：

year | total_profit
2000 | 3750
2001 | 3000
NULL | 6750  -- 總計

2. 多層分組 + ROLLUP（核心威力）

SELECT year, country, product, SUM(profit) AS total_profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;

結果（關鍵行標註）：

year | country  | product    | total_profit
2000 | Finland  | Computer   | 1500
2000 | Finland  | Phone      | 100
2000 | Finland  | NULL       | 1600   -- Finland 2000 小計
2000 | India    | Calculator | 150
2000 | India    | NULL       | 150    -- India 2000 小計
2000 | USA      | Computer   | 2000
2000 | USA      | NULL       | 2000   -- USA 2000 小計
2000 | NULL     | NULL       | 3750   -- 2000 年總計
2001 | Finland  | Phone      | 200
2001 | India    | Calculator | 300
2001 | USA      | Computer   | 2500
2001 | NULL     | NULL       | 3000   -- 2001 年總計
NULL | NULL     | NULL       | 6750   -- 總總計

識別彙總行：GROUPING() 函數

區分「資料本身是 NULL」還是「彙總行產生的 NULL」：

SELECT 
    year, country, product, SUM(profit) AS total_profit,
    GROUPING(year) AS gy,
    GROUPING(country) AS gc,
    GROUPING(product) AS gp
FROM sales
GROUP BY year, country, product WITH ROLLUP;

GROUPING() 回傳 1 表示該欄位為彙總產生的 NULL，0 表示正常資料值。

美化輸出：用 CASE 替換 NULL 標籤

SELECT 
    CASE WHEN GROUPING(year) = 1 THEN '所有年份' ELSE CAST(year AS CHAR) END AS 年份,
    CASE WHEN GROUPING(country) = 1 THEN '所有國家' ELSE country END AS 國家,
    CASE WHEN GROUPING(product) = 1 THEN '所有產品' ELSE product END AS 產品,
    SUM(profit) AS 總利潤
FROM sales
GROUP BY year, country, product WITH ROLLUP;

進階應用場景

1. 銷售儀表板：年 → 季 → 月 鑽取

SELECT 
    YEAR(order_date) AS 年,
    QUARTER(order_date) AS 季,
    MONTH(order_date) AS 月,
    SUM(amount) AS 銷售額
FROM orders
GROUP BY 年, 季, 月 WITH ROLLUP;

2. 庫存報表：倉庫 → 分類 → 商品

SELECT 
    warehouse, category, product,
    SUM(qty) AS 總庫存,
    GROUPING(warehouse) AS is_warehouse_total,
    GROUPING(category) AS is_category_total
FROM inventory
GROUP BY warehouse, category, product WITH ROLLUP;

3. 搭配 HAVING 過濾彙總層級

-- 只顯示年總計超過 5000 的年份（含其明細）
SELECT year, country, product, SUM(profit)
FROM sales
GROUP BY year, country, product WITH ROLLUP
HAVING (GROUPING(year) = 0 AND SUM(profit) > 5000)
    OR GROUPING(year) = 1;  -- 保留總總計行

效能與限制

• 排序順序固定：依 GROUP BY 欄位順序，無法自訂彙總行位置
• 不支援 LIMIT：會限制包含彙總行的總輸出筆數
• 大資料集慎用：產生行數 = 明細行 + 彙總行，可能龐大
• 索引優化：INDEX idx_rollup (year, country, product) 加速分組

替代方案：UNION ALL 手動彙總

需自訂彙總順序、格式或分頁時：

-- 明細
SELECT year, country, product, SUM(profit), 'detail' AS level FROM sales GROUP BY year, country, product
UNION ALL
-- 國家小計
SELECT year, country, NULL, SUM(profit), 'country_subtotal' FROM sales GROUP BY year, country
UNION ALL
-- 年總計
SELECT year, NULL, NULL, SUM(profit), 'year_total' FROM sales GROUP BY year
UNION ALL
-- 總計
SELECT NULL, NULL, NULL, SUM(profit), 'grand_total' FROM sales
ORDER BY year, country, product;

PHP 報表輸出範例

<?php
$sql = "SELECT 
    CASE WHEN GROUPING(year)=1 THEN '總計' ELSE year END AS year,
    CASE WHEN GROUPING(country)=1 THEN '小計' ELSE country END AS country,
    product,
    SUM(profit) AS profit,
    GROUPING(year) AS gy,
    GROUPING(country) AS gc
FROM sales
GROUP BY year, country, product WITH ROLLUP";

$result = $conn->query($sql);
echo "";
echo "";
while ($row = $result->fetch_assoc()) {
    $level = $row['gy'] ? '總計' : ($row['gc'] ? '年小計' : '明細');
    $style = $row['gy'] ? 'font-weight:bold;background:#f0f0f0' : ($row['gc'] ? 'font-weight:bold' : '');
    echo "";
}
echo "
年份
國家
產品
利潤
層級

        
{$row['year']}
        
{$row['country']}
        
{$row['product'] ?? '—'}
        
{$row['profit']}
        
$level
    
";
?>

WITH ROLLUP 讓單一查詢產生完整多層報表，配合 GROUPING() 識別層級，是 SQL 報表開發的神兵利器！