萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL/MariaDB 將查詢結果多列轉單列的三種實用方法

前言

在報表統計、資料匯出等場景中,常需將橫向的多列資料轉為縱向的單列多行。例如將 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);

方法一:UNION ALL 多查詢合併

SELECT c1 FROM test
UNION ALL
SELECT c2 FROM test
UNION ALL
SELECT c3 FROM test;
  • 優點:語法直觀,易理解維護,標準 SQL 相容性好
  • 缺點:需掃描表 N 次 (N=列數),大表效能差
  • 適用:小資料量、臨時查詢、列數固定少

方法二:連接符 + 外部工具處理

mysql -Bse "SELECT CONCAT(c1,'@@',c2,'@@',c3) FROM test;" | sed 's/@@/\n/g'
  • 原理:SQL 端用特殊分隔符 (如 @@) 串聯欄位,Shell 端用 sed 替換為換行
  • 優點:單次掃描表,資料庫負載低
  • 缺點:依賴外部工具 (sed/awk),不適合純 SQL 環境 (如存儲過程、應用程式直連)
  • 適用:命令列匯出、ETL 腳本、大資料量單次導出

方法三:變數模擬行號 + CASE WHEN 動態選列

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;
  • 原理:
    1. 子查詢生成遞增行號 rownum (1,2,3,1,2,3... 循環)
    2. 與原表做笛卡爾積,產生 行數×列數 行
    3. CASE WHEN 根據行號動態選取對應列
  • 優點:純 SQL 實現,單次掃描,無外部依賴
  • 缺點:邏輯較複雜,維護成本高;笛卡爾積可能產生大量中間結果
  • 適用:需在存儲過程/視圖/應用 SQL 中直接使用、列數固定

效能對比與選型建議

方法掃描次數外部依賴複雜度推薦場景
UNION ALLN 次無低小表、列數少、臨時查詢
連接符+外部工具1 次有低大表導出、命令列/腳本環境
變數+CASE WHEN1 次無高應用層 SQL、存儲過程、固定列數

進階:動態列數處理

若列數不固定,建議在應用層 (Python/Java 等) 讀取結果集後轉置,或使用預處理動態構建 UNION SQL。純 SQL 動態列轉行較為繁瑣,不建議在資料庫層強行實現。

copyright © 萬盛學電腦網 all rights reserved