MySQL 交叉表（透視表）實現全方案：從靜態 CASE WHEN 到動態 PREPARE 自動生成列

樣本數據

CREATE TABLE tx(id INT PRIMARY KEY, c1 CHAR(2), c2 CHAR(2), c3 INT);
INSERT INTO tx VALUES
(1,'A1','B1',9),(2,'A2','B1',7),(3,'A3','B1',4),(4,'A4','B1',2),
(5,'A1','B2',2),(6,'A2','B2',9),(7,'A3','B2',8),(8,'A4','B2',5),
(9,'A1','B3',1),(10,'A2','B3',8),(11,'A3','B3',8),(12,'A4','B3',6),
(13,'A1','B4',8),(14,'A2','B4',2),(15,'A3','B4',6),(16,'A4','B4',9),
(17,'A1','B4',3),(18,'A2','B4',5),(19,'A3','B4',2),(20,'A4','B4',5);

目標結果

+------+-----+-----+-----+-----+------+
| C1   | B1  | B2  | B3  | B4  | Total|
+------+-----+-----+-----+-----+------+
| A1   | 9   | 2   | 1   | 11  | 23   |
| A2   | 7   | 9   | 8   | 7   | 31   |
| A3   | 4   | 8   | 8   | 8   | 28   |
| A4   | 2   | 5   | 6   | 14  | 27   |
| Total| 22  | 24  | 23  | 40  | 109  |
+------+-----+-----+-----+-----+------+

方案一：SUM(IF) + WITH ROLLUP（推薦）

SELECT 
  IFNULL(c1,'Total') AS C1,
  SUM(IF(c2='B1',c3,0)) AS B1,
  SUM(IF(c2='B2',c3,0)) AS B2,
  SUM(IF(c2='B3',c3,0)) AS B3,
  SUM(IF(c2='B4',c3,0)) AS B4,
  SUM(c3) AS Total
FROM tx
GROUP BY c1 WITH ROLLUP;

• WITH ROLLUP 自動生成匯總行；
• IFNULL 將 NULL 顯示為 Total。

方案二：SUM(IF) + UNION 手工匯總

SELECT c1,
  SUM(IF(c2='B1',c3,0)) B1,
  SUM(IF(c2='B2',c3,0)) B2,
  SUM(IF(c2='B3',c3,0)) B3,
  SUM(IF(c2='B4',c3,0)) B4,
  SUM(c3) Total
FROM tx GROUP BY c1
UNION ALL
SELECT 'Total',
  SUM(IF(c2='B1',c3,0)),
  SUM(IF(c2='B2',c3,0)),
  SUM(IF(c2='B3',c3,0)),
  SUM(IF(c2='B4',c3,0)),
  SUM(c3)
FROM tx;

方案三：子查詢預聚合 + WITH ROLLUP（性能更優）

SELECT 
  IFNULL(c1,'Total') C1,
  SUM(IF(c2='B1',c3,0)) B1,
  SUM(IF(c2='B2',c3,0)) B2,
  SUM(IF(c2='B3',c3,0)) B3,
  SUM(IF(c2='B4',c3,0)) B4,
  SUM(c3) Total
FROM (
  SELECT c1, c2, SUM(c3) c3
  FROM tx GROUP BY c1, c2 WITH ROLLUP
  HAVING c1 IS NOT NULL
) t
GROUP BY c1 WITH ROLLUP;

方案四：動態列生成（適用於列值不固定）

SET @sql = '';
SELECT @sql := CONCAT(@sql, 'SUM(IF(c2=', QUOTE(c2), ',c3,0)) AS ', QUOTE(c2), ',')
FROM (SELECT DISTINCT c2 FROM tx) t;

SET @sql = CONCAT('SELECT IFNULL(c1,\'Total\'), ', LEFT(@sql, LENGTH(@sql)-1),
                  ', SUM(c3) Total FROM tx GROUP BY c1 WITH ROLLUP');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• 先查詢所有唯一 c2 值拼接列定義；
• 再構建完整 SQL 並通過 PREPARE/EXECUTE 執行；
• 新增 c2 值無需改代碼自動適配。

等價替換提示

IF(cond, true, false) 可替換為標準 SQL CASE WHEN cond THEN true ELSE false END 或 Oracle DECODE，跨庫兼容性更好。