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

SQL 列轉行實戰：UNION ALL 將寬表轉長表

編輯：MySQL 綜合教學

SQL 列轉行實戰：UNION ALL 將寬表轉長表

將「寬表」 (Wide Format) 轉為「長表」 (Long Format) 是資料整理、ETL、BI 建模常見需求。標準 SQL 使用 UNION ALL 逐欄位拆解再合併，跨資料庫通用、效能穩定。

來源資料：學生成績寬表

CREATE TABLE score_wide (
    name VARCHAR(20),
    yw INT,   -- 語文
    sx INT    -- 數學
);

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

寬表結構：

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

目標：長表 (每科一行)

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

核心 SQL：UNION ALL 逐欄拆解

SELECT name, 'yw' AS subject, yw AS score FROM score_wide
UNION ALL
SELECT name, 'sx' AS subject, sx AS score FROM score_wide
ORDER BY name, subject;

執行邏輯圖解

第一個 SELECT：取出 name + yw，產生虛擬欄位 subject='yw', score=yw
第二個 SELECT：取出 name + sx，產生虛擬欄位 subject='sx', score=sx
UNION ALL：垂直合併兩結果集 (不去重，效能優於 UNION)
ORDER BY：最終排序輸出

寬表結構寬表資料轉換結果轉換過程圖解

進階變體

1. 動態欄位 (未知科目數) - 應用層組裝 SQL

# Python 範例
columns = ['yw', 'sx', 'en', 'ph']  # 從資料字典或 INFORMATION_SCHEMA 取得
selects = [f"SELECT name, '{c}' AS subject, {c} AS score FROM score_wide" for c in columns]
sql = " UNION ALL ".join(selects) + " ORDER BY name, subject"

2. 保留 NULL 值 (學生缺考)

# UNION ALL 會保留 NULL，若需過濾
SELECT name, subject, score FROM (
    SELECT name, 'yw' AS subject, yw AS score FROM score_wide
    UNION ALL
    SELECT name, 'sx' AS subject, sx AS score FROM score_wide
) t
WHERE score IS NOT NULL;

3. 多指標同時轉行 (分數+等第)

SELECT name, subject, score, grade FROM (
    SELECT name, 'yw' AS subject, yw AS score, yw_grade AS grade FROM score_wide
    UNION ALL
    SELECT name, 'sx' AS subject, sx AS score, sx_grade AS grade FROM score_wide
) t;

各資料庫原生 UNPIVOT 語法對比

資料庫	語法	範例
SQL Server	UNPIVOT	`SELECT name, subject, score FROM score_wide UNPIVOT (score FOR subject IN (yw, sx)) u`
Oracle 11g+	UNPIVOT	`SELECT * FROM score_wide UNPIVOT (score FOR subject IN (yw, sx))`
PostgreSQL	無原生	使用 UNION ALL 或 jsonb_each_text
MySQL	無原生	使用 UNION ALL (本文方法)
Spark SQL	stack	`df.selectExpr("name", "stack(2, 'yw', yw, 'sx', sx) as (subject, score)")`

UNION ALL vs UNION 效能差異

# UNION: 需排序去重，複雜度 O(N log N)
# UNION ALL: 直接串接，複雜度 O(N)，無去重開銷
# 列轉行場景源資料無重複，必須用 UNION ALL

常見錯誤

錯誤	後果	修正
使用 UNION 而非 UNION ALL	效能差、若不同學生同分數會被誤去重	改用 UNION ALL
欄位順序/型別不一致	報錯或隱含轉換錯誤	各 SELECT 欄位數、順序、型別完全對齊
忘記常數欄位 (subject)	無法識別來源欄位	每 SELECT 必須帶上 `'yw' AS subject`

效能優化建議

寬表有索引時，各 SELECT 可獨立走索引掃描。
超大寬表 (百萬行 x 百欄位) 考慮分批處理或使用資料庫原生 UNPIVOT/STACK。
ETL 流程中，列轉行通常發生在 Staging 層，建議用 INSERT INTO target SELECT ... UNION ALL ... 直寫目標表。

UNION ALL 列轉行：簡單、標準、高效，是 SQL 開發者工具箱必備技能。