MySQL 高級查詢：GROUP BY 進階用法 - GROUP_CONCAT 與 WITH ROLLUP 實戰詳解

一、GROUP_CONCAT：將分組內多行聚合為單字符串

基礎語法

SELECT 
    student_name,
    GROUP_CONCAT(test_score) AS scores
FROM student
GROUP BY student_name;

結果：每位學生一行，scores 為該生所有分數用逗號連接的字符串。

進階參數全解析

SELECT 
    student_name,
    GROUP_CONCAT(
        DISTINCT test_score                -- 去重
        ORDER BY test_score DESC           -- 組內排序
        SEPARATOR ' | '                    -- 自定義分隔符 (默認逗號)
    ) AS scores
FROM student
GROUP BY student_name;

關鍵限制與應對

• 長度截斷：默認 group_concat_max_len = 1024 字節，超長截斷。
  • 會話級調大：SET SESSION group_concat_max_len = 1000000;
  • 全局調大：配置文件 group_concat_max_len = 1000000 (最大 4GB)。
• 性能陷阱**：大量行聚合字符串拼接消耗 CPU/內存，結果集膨脹。慎用於大分組 (如 > 1000 行/組)。
• NULL 值忽略**：GROUP_CONCAT 自動跳過 NULL，不需額外處理。

實戰場景

1. 標籤聚合**：文章標籤表 → GROUP_CONCAT(tag_name SEPARATOR ',') 一行顯示所有標籤。
2. 關聯 ID 收集**：訂單商品表 → GROUP_CONCAT(product_id ORDER BY created_at) 保持下單順序。
3. 導出報表**：部門員工姓名列表、錯誤代碼彙總等。

二、WITH ROLLUP：多維度小計與總計一條 SQL 搞定

基礎語法

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

結果解讀

+------+-------------+
| year | total_profit |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |  <-- 總計行 (ROLLUP 生成)
+------+-------------+

NULL 代表該維度的總計 (Super-aggregate)。

多維度 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   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |  <-- 國家小計 (India 2000)
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |  <-- 國家小計 (USA 2000)
| 2000 | NULL    | NULL       |        4525 |  <-- 年度小計 (2000)
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |  <-- 國家小計
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |  <-- 國家小計
| 2001 | NULL    | NULL       |        3010 |  <-- 年度小計 (2001)
| NULL | NULL    | NULL       |        7535 |  <-- 總計
+------+---------+------------+-------------+

規則：**每當 GROUP BY 列表中除最後一列外的任意列值發生變化 (Break)，即產生一個高層小計行**。

限制與技巧

• 互斥 ORDER BY：WITH ROLLUP 禁止同時使用 ORDER BY。但 GROUP BY 列支持 ASC/DESC 控制輸出順序 (小計行仍緊跟其明細行之後)。
• 識別小計行**：應用層判斷 IS NULL 列，或用 GROUPING() 函數 (8.0+) 精確識別哪層小計：GROUPING(year), GROUPING(country), GROUPING(product) 返回 1 表示該列為聚合產生的 NULL。
• LIMIT 截斷風險**：LIMIT 會切掉 ROLLUP 生成的小計行，**勿配合使用**。

三、組合拳：GROUP_CONCAT + WITH ROLLUP

SELECT 
    year, country,
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ', ') AS products,
    SUM(profit) AS total_profit
FROM sales
GROUP BY year, country WITH ROLLUP;

輸出：每個國家的產品列表 + 利潤小計，年度彙總產品列表 + 年度利潤，總計行。

四、性能與替代方案

• 大數據量**：GROUP_CONCAT 內存壓力大，WITH ROLLUP 增加排序/分組開銷。考慮應用層聚合或 ClickHouse/Doris 等 OLAP 引擎。
• 窗口函數替代 (8.0+)**：SUM() OVER (PARTITION BY year) 可計算小計不改變行數，配合 ROW_NUMBER() 標記小計行，靈活性更高。