MySQL 行轉列實戰：CASE WHEN 配合 UNION 實現動態樞紐分析

MySQL 行轉列實戰：CASE WHEN 配合 UNION 實現動態樞紐分析

MySQL 5.7 之前無原生 PIVOT 語法，常用 CASE WHEN + 聚合函數實現行轉列。本文以帳單（bill）與卡片模型（card_model）為例，示範如何將多類型消費金額按店鋪樞紐為寬表。

資料模型

-- 帳單表
CREATE TABLE bill (
  id CHAR(36) PRIMARY KEY,
  customer INT, shop INT, money DECIMAL(10,2), type INT
) ENGINE=InnoDB;

-- 卡片模型表
CREATE TABLE card_model (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(255), shop INT, v1 DECIMAL(10,2), type INT
) ENGINE=InnoDB;

目標輸出

每家店鋪一列，欄位為：type1 總額、type2 總額、card 總額、總和。

步驟 1：UNION 將三類資料統一為長表

SELECT IFNULL(SUM(b.money),0) AS money, b.shop, 'type1' AS type_test
FROM bill b WHERE b.type = 1 GROUP BY b.shop
UNION ALL
SELECT IFNULL(SUM(b.money),0), b.shop, 'type2'
FROM bill b WHERE b.type = 2 GROUP BY b.shop
UNION ALL
SELECT IFNULL(SUM(c.v1),0), c.shop, 'card'
FROM card_model c GROUP BY c.shop

• IFNULL 避免無資料時回傳 NULL 導致後續聚合失效。
• 別名 money、shop、type_test 統一三段欄位結構。
• 使用 UNION ALL 而非 UNION，避免額外去重排序開銷（此處無重複鍵）。

步驟 2：外層 CASE WHEN 條件聚合實現行轉列

SELECT 
  a.shop AS '店鋪',
  SUM(CASE a.type_test WHEN 'type1' THEN a.money ELSE 0 END) AS 'type1',
  SUM(CASE a.type_test WHEN 'type2' THEN a.money ELSE 0 END) AS 'type2',
  SUM(CASE a.type_test WHEN 'card'  THEN a.money ELSE 0 END) AS 'card',
  SUM(a.money) AS '總和'
FROM (
  -- 上述 UNION 查詢
) a
GROUP BY a.shop;

完整單句 SQL

SELECT 
  a.shop AS '店鋪',
  SUM(CASE a.type_test WHEN 'type1' THEN a.money ELSE 0 END) AS 'type1',
  SUM(CASE a.type_test WHEN 'type2' THEN a.money ELSE 0 END) AS 'type2',
  SUM(CASE a.type_test WHEN 'card'  THEN a.money ELSE 0 END) AS 'card',
  SUM(a.money) AS '總和'
FROM (
  SELECT IFNULL(SUM(b.money),0) money, b.shop, 'type1' type_test FROM bill b WHERE b.type=1 GROUP BY b.shop
  UNION ALL
  SELECT IFNULL(SUM(b.money),0), b.shop, 'type2' FROM bill b WHERE b.type=2 GROUP BY b.shop
  UNION ALL
  SELECT IFNULL(SUM(c.v1),0), c.shop, 'card' FROM card_model c GROUP BY c.shop
) a
GROUP BY a.shop;

進階：動態類型（未知 type 值）

若 type 值不固定，需動態構建 SQL：

1. 先查 SELECT DISTINCT type FROM bill 取得所有類型。
2. 應用層拼接 SUM(CASE WHEN type_test='typeX' THEN money END) AS typeX。
3. 執行動態 SQL（Prepared Statement 或應用層組裝）。

效能建議

• 來源表 shop、type 建立複合索引 (shop, type)。
• 資料量大時，考慮物化視圖或摘要表定時重建。
• MySQL 8.0+ 可嘗試 WITH (CTE) 提升可讀性。

此模式廣泛適用於銷售報表、多維度統計等場景，掌握 UNION + CASE WHEN 範式即可靈活應對行轉列需求。