在報表統計、資料匯出等場景中,常需將橫向的多列資料轉為縱向的單列多行。例如將 3 欄 2 列的資料:
1 2 3
4 5 6
轉換為 6 行 1 列:
1
2
3
4
5
6
本文介紹三種實現方案,各有優劣,可根據資料量與環境選擇。
CREATE TABLE test(c1 INT, c2 INT, c3 INT);
INSERT INTO test VALUES(1,2,3),(4,5,6),(7,8,9),(10,11,12);
SELECT c1 FROM test
UNION ALL
SELECT c2 FROM test
UNION ALL
SELECT c3 FROM test;
mysql -Bse "SELECT CONCAT(c1,'@@',c2,'@@',c3) FROM test;" | sed 's/@@/\n/g'
@@) 串聯欄位,Shell 端用 sed 替換為換行SELECT CASE
WHEN rownum=1 THEN c1
WHEN rownum=2 THEN c2
WHEN rownum=3 THEN c3
ELSE ''
END
FROM test,
(SELECT @rownum:=@rownum+1 AS rownum FROM (SELECT @rownum:=0) r, test) b;
rownum (1,2,3,1,2,3... 循環)CASE WHEN 根據行號動態選取對應列| 方法 | 掃描次數 | 外部依賴 | 複雜度 | 推薦場景 |
|---|---|---|---|---|
| UNION ALL | N 次 | 無 | 低 | 小表、列數少、臨時查詢 |
| 連接符+外部工具 | 1 次 | 有 | 低 | 大表導出、命令列/腳本環境 |
| 變數+CASE WHEN | 1 次 | 無 | 高 | 應用層 SQL、存儲過程、固定列數 |
若列數不固定,建議在應用層 (Python/Java 等) 讀取結果集後轉置,或使用預處理動態構建 UNION SQL。純 SQL 動態列轉行較為繁瑣,不建議在資料庫層強行實現。