MySQL 列轉行函數全方位解析

一、概述

MySQL 列轉行函數（Pivot 轉換或數據透視）是將列中的數據轉換成行的形式，可以在大量數據分析中用於數據聚合。在 MySQL 中，有三種方法可以實現列轉行功能：

• 使用 CASE WHEN 語句
• 使用 GROUP_CONCAT 函數
• 使用 UNION ALL 語句

本文將詳細介紹這三種方法的原理和實現步驟。

二、使用 CASE WHEN 語句

使用 CASE WHEN 語句是實現列轉行功能最常見的方法。其原理是將每個需要轉置的列都分別用 CASE WHEN 語句進行處理，將所有處理後的結果用 UNION ALL 連接起來。

下面是一個例子：

SELECT id, 
    MAX(CASE WHEN subject = 'math' THEN score ELSE NULL END) AS math, 
    MAX(CASE WHEN subject = 'english' THEN score ELSE NULL END) AS english, 
    MAX(CASE WHEN subject = 'history' THEN score ELSE NULL END) AS history 
FROM scores
GROUP BY id;

以上代碼將 scores 表中的 id、subject 和 score 三個字段進行列轉行，其結果將會是以下形式：

+------+----------+---------+---------+
| id   | math     | english | history |
+------+----------+---------+---------+
| 1    | 80       | 90      | 85      |
| 2    | 75       | 85      | 90      |
| 3    | 95       | 92      | 80      |
+------+----------+---------+---------+

以上代碼使用了 MAX 函數，因為涉及到多個 CASE WHEN 語句，每個語句只有一條記錄會生效，其他記錄為 NULL。因此，可以使用 MAX 函數來將有效記錄取出。

三、使用 GROUP_CONCAT 函數

GROUP_CONCAT 函數是用於將多行記錄轉換成單行記錄字符串的函數。使用該函數實現列轉行功能的原理是將每個需要轉置的數據分別使用聚合函數和 GROUP BY 語句進行處理，並將結果通過 GROUP_CONCAT 函數連接起來。

下面是一個例子：

SELECT id, 
    GROUP_CONCAT(CASE WHEN subject = 'math' THEN score ELSE NULL END) AS math, 
    GROUP_CONCAT(CASE WHEN subject = 'english' THEN score ELSE NULL END) AS english, 
    GROUP_CONCAT(CASE WHEN subject = 'history' THEN score ELSE NULL END) AS history 
FROM scores
GROUP BY id;

以上代碼得到的結果與使用 CASE WHEN 語句的結果相同。但是，使用 GROUP_CONCAT 函數時需要注意被連接的字符串可能會超出 MySQL 默認配置的長度限制，因此需要事先進行調整。

四、使用 UNION ALL 語句

使用 UNION ALL 語句實現列轉行功能的原理是將所有需要轉置的列都分別作為子查詢，並在最外層使用 UNION ALL 連接起來。

下面是一個例子：

SELECT id, 'math' AS subject, math AS score FROM scores 
UNION ALL
SELECT id, 'english' AS subject, english AS score FROM scores 
UNION ALL
SELECT id, 'history' AS subject, history AS score FROM scores 
ORDER BY id, subject;

以上代碼得到的結果與使用前兩種方法的結果相同。但是，由於需要多次查詢，因此性能相對較差，適用於數據量比較小的情況。

五、注意事項

在使用列轉行函數時，需要注意以下幾點：

• 需要將轉置後的數據進行聚合，因此需要使用 GROUP BY 語句。
• 需要處理的字段需要在 GROUP BY 語句中出現。
• 使用 CASE WHEN 語句時，需要每個需要轉置的字段都使用一個 CASE WHEN 語句進行處理。
• 使用 GROUP_CONCAT 函數時，需要將需要連接的字符串進行長度控制。

六、總結

MySQL 列轉行函數是一種將列數據轉換成行數據的常用方法。在 MySQL 中，主要有三種方法可以實現列轉行功能：使用 CASE WHEN 語句、使用 GROUP_CONCAT 函數和使用 UNION ALL 連接多個子查詢。使用列轉行函數時，需要注意需要轉換的字段出現在 GROUP BY 語句中，同時使用 MAX 函數或 GROUP_CONCAT 函數選擇要取出的有效記錄。為了避免字符串長度超出 MySQL 默認配置的限制，使用 GROUP_CONCAT 函數時需要事先進行長度控制。