您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 行轉列 (Pivot) 實戰：CASE 聚合法完整教學

編輯：MySQL 綜合教學

SQL 行轉列 (Pivot) 實戰：CASE 聚合法完整教學

將「長表」 (Long Format) 轉為「寬表」 (Wide Format) 是報表開發常見需求。標準 SQL 採用 CASE WHEN ... THEN ... END 搭配聚合函數實現，跨資料庫通用。

來源資料：學生成績長表

CREATE TABLE student_score (
    name VARCHAR(20),
    subject VARCHAR(10),  -- 'yw'=語文, 'sx'=數學
    score INT
);

INSERT INTO student_score VALUES
('小明', 'yw', 85), ('小明', 'sx', 92),
('小紅', 'yw', 78), ('小紅', 'sx', 88),
('小剛', 'yw', 90), ('小剛', 'sx', 95);

長表結構：

+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 小明 | yw      | 85    |
| 小明 | sx      | 92    |
| 小紅 | yw      | 78    |
| 小紅 | sx      | 88    |
| 小剛 | yw      | 90    |
| 小剛 | sx      | 95    |
+------+---------+-------+

目標：寬表 (每學生一行，科目變欄位)

+------+------+------+
| name | yw   | sx   |
+------+------+------+
| 小明 | 85   | 92   |
| 小紅 | 78   | 88   |
| 小剛 | 90   | 95   |
+------+------+------+

核心 SQL：CASE + MAX + GROUP BY

SELECT 
    name,
    MAX(CASE WHEN subject = 'yw' THEN score END) AS yw,
    MAX(CASE WHEN subject = 'sx' THEN score END) AS sx
FROM student_score
GROUP BY name;

執行邏輯圖解

GROUP BY name：將 6 筆資料分為 3 組 (小明、小紅、小剛)
CASE WHEN：在每組內，依 subject 篩選分數
- 小明組：yw→85, sx→92, 其他→NULL
MAX()：每組每欄位取最大值 (非 NULL 唯一值即為該值)
- MAX(85, NULL) = 85 (yw)
- MAX(92, NULL) = 92 (sx)

表結構示意原始資料轉換結果轉換過程圖解

進階變體

1. 動態科目 (未知欄位數) - 需動態 SQL

# MySQL 預存程序範例
SELECT GROUP_CONCAT(DISTINCT 
    CONCAT('MAX(CASE WHEN subject = ''', subject, ''' THEN score END) AS ', subject)
) INTO @sql FROM student_score;
SET @sql = CONCAT('SELECT name, ', @sql, ' FROM student_score GROUP BY name');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

2. 多指標樞紐 (分數+排名)

SELECT 
    name,
    MAX(CASE WHEN subject='yw' THEN score END) AS yw_score,
    MAX(CASE WHEN subject='yw' THEN rank END) AS yw_rank,
    MAX(CASE WHEN subject='sx' THEN score END) AS sx_score,
    MAX(CASE WHEN subject='sx' THEN rank END) AS sx_rank
FROM student_score_with_rank
GROUP BY name;

3. 總計行 (ROLLUP)

SELECT 
    COALESCE(name, '總平均') AS name,
    AVG(CASE WHEN subject='yw' THEN score END) AS yw_avg,
    AVG(CASE WHEN subject='sx' THEN score END) AS sx_avg
FROM student_score
GROUP BY name WITH ROLLUP;

各資料庫原生 PIVOT 語法對比

資料庫	語法	範例
SQL Server	PIVOT	`SELECT * FROM (SELECT name, subject, score FROM t) src PIVOT (MAX(score) FOR subject IN ([yw],[sx])) pvt`
Oracle 11g+	PIVOT	`SELECT * FROM t PIVOT (MAX(score) FOR subject IN ('yw' AS yw, 'sx' AS sx))`
PostgreSQL	crosstab (tablefunc)	需安裝擴充套件，較複雜
MySQL	無原生	使用 CASE 聚合法 (本文方法)
Spark SQL	pivot	`df.groupBy("name").pivot("subject").max("score")`

常見錯誤與修正

錯誤寫法	問題	修正
`CASE WHEN subject='yw' THEN score ELSE 0 END`	分數可能為 0，ELSE 0 會干擾 MAX	省略 ELSE (預設 NULL)，MAX 自動忽略 NULL
使用 SUM 而非 MAX	若同學生同科目有多筆 (重考)，SUM 會加總	用 MAX/MIN/AVG 視業務定義
忘記 GROUP BY	單行結果，非樞紐	必須 GROUP BY 維度欄位 (name)

效能提示

在 (name, subject) 建立複合索引，加速分組與篩選。
大資料量可先聚合再樞紐：WITH agg AS (SELECT name, subject, MAX(score) AS score FROM t GROUP BY name, subject) SELECT ...
動態欄位場景，應用層組裝 SQL 比資料庫動態 SQL 更易維護。

掌握 CASE 聚合法，無需依賴資料庫專有語法，即可在任何支援標準 SQL 的系統實現行轉列。