當(dāng)前位置 主頁(yè) > 技術(shù)大全 >
MySQL中的"行轉(zhuǎn)列"是一個(gè)經(jīng)典且非常實(shí)用的數(shù)據(jù)處理技巧，通常用于將數(shù)據(jù)從一種易于存儲(chǔ)的格式轉(zhuǎn)換為一種更易于閱讀和報(bào)告的格式。下面我將詳細(xì)解釋其概念、方法和應(yīng)用場(chǎng)景。
核心概念：什么是行轉(zhuǎn)列？
| 學(xué)生姓名 | 科目 | 成績(jī) |
| :------- | :--- | :--- |
| 張三 | 語(yǔ)文 | 90 |
| 張三 | 數(shù)學(xué) | 85 |
| 李四 | 語(yǔ)文 | 95 |
| 李四 | 數(shù)學(xué) | 70 |
科目）轉(zhuǎn)換為新的列名，并將其對(duì)應(yīng)的值（如成績(jī)）填充到新列下。| 學(xué)生姓名 | 語(yǔ)文 | 數(shù)學(xué) |
| :------- | :--- | :--- |
| 張三 | 90 | 85 |
| 李四 | 95 | 70 |
這種轉(zhuǎn)換也稱為數(shù)據(jù)透視（PIVOT）。
假設(shè)我們有上文的成績(jī)表 scores。
方法一：使用 CASE WHEN + 聚合函數(shù)（最通用、最靈活）
這是最經(jīng)典和兼容性最好的方法，適用于幾乎所有版本的 MySQL。
思路：
GROUP BY 按唯一標(biāo)識(shí)（如學(xué)生姓名）分組，確保一行一個(gè)學(xué)生。CASE WHEN（或 IF）語(yǔ)句判斷并提取對(duì)應(yīng)的值。MAX 或 MIN、SUM 等聚合函數(shù)對(duì)提取出的值進(jìn)行聚合（通常用于確保每個(gè)分組下只有一個(gè)非空值）。SQL 示例：
SELECT
學(xué)生姓名,
MAX(CASE WHEN 科目 = '語(yǔ)文' THEN 成績(jī) ELSE NULL END) AS 語(yǔ)文,
MAX(CASE WHEN 科目 = '數(shù)學(xué)' THEN 成績(jī) ELSE NULL END) AS 數(shù)學(xué)
-- , ...可以繼續(xù)添加其他科目
FROM scores
GROUP BY 學(xué)生姓名;
結(jié)果：
| 學(xué)生姓名 | 語(yǔ)文 | 數(shù)學(xué) |
| :------- | :--- | :--- |
| 張三 | 90 | 85 |
| 李四 | 95 | 70 |
為什么用 MAX？
因?yàn)?GROUP BY 后，每個(gè)學(xué)生的每門科目理論上只有一條記錄。MAX 的作用是取回那條唯一記錄的值。如果不用聚合函數(shù)，SQL 會(huì)報(bào)錯(cuò)。你也可以用 MIN 或 SUM，效果相同。
方法二：使用 IF() + 聚合函數(shù)（MySQL 的簡(jiǎn)潔寫法）
IF(condition, value_if_true, value_if_false) 是 CASE WHEN 的簡(jiǎn)化版，邏輯更清晰。
SQL 示例：
SELECT
學(xué)生姓名,
MAX(IF(科目 = '語(yǔ)文', 成績(jī), NULL)) AS 語(yǔ)文,
MAX(IF(科目 = '數(shù)學(xué)', 成績(jī), NULL)) AS 數(shù)學(xué)
FROM scores
GROUP BY 學(xué)生姓名;
效果與方法一完全相同。
方法三：使用 GROUP_CONCAT（另一種形式的“行轉(zhuǎn)列”）
有時(shí)“轉(zhuǎn)列”的目的不是生成多個(gè)新列，而是將多行數(shù)據(jù)合并到一個(gè)單元格中，用分隔符連接。這時(shí) GROUP_CONCAT 非常有用。
SQL 示例：
SELECT
學(xué)生姓名,
GROUP_CONCAT(科目, ':', 成績(jī) SEPARATOR '; ') AS 成績(jī)單
FROM scores
GROUP BY 學(xué)生姓名;
結(jié)果：
| 學(xué)生姓名 | 成績(jī)單 |
| :------- | :----------------- |
| 張三 | 語(yǔ)文:90; 數(shù)學(xué):85 |
| 李四 | 語(yǔ)文:95; 數(shù)學(xué):70 |
高級(jí)應(yīng)用：動(dòng)態(tài)行轉(zhuǎn)列
上面的方法有個(gè)巨大缺陷：需要手動(dòng)編寫每個(gè)要轉(zhuǎn)換的列值（如‘語(yǔ)文’、‘數(shù)學(xué)’）。如果科目是不確定的（例如隨時(shí)會(huì)增加‘英語(yǔ)’、‘物理’），上面的 SQL 就無(wú)法滿足需求。
這時(shí)需要使用存儲(chǔ)過程（Stored Procedure） 來(lái)動(dòng)態(tài)生成 SQL 語(yǔ)句。
思路：
CONCAT, GROUP_CONCAT）構(gòu)造出包含所有 CASE WHEN 語(yǔ)句的 SQL 字符串。PREPARE & EXECUTE）來(lái)執(zhí)行這個(gè)動(dòng)態(tài)生成的 SQL 字符串。示例代碼：
-- 1. 定義變量存儲(chǔ)動(dòng)態(tài)生成的SQL
SET @sql = NULL;
-- 2. 查詢所有不重復(fù)的科目，并拼接成 MAX(CASE WHEN...) 的格式
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(科目 = ''',
科目,
''', 成績(jī), NULL)) AS ',
CONCAT('`', 科目, '`') -- 用反引號(hào)包裹科目名，防止它是關(guān)鍵字
)
) INTO @sql
FROM scores;
-- 3. 拼接完整的SQL語(yǔ)句
SET @sql = CONCAT('SELECT 學(xué)生姓名, ', @sql, ' FROM scores GROUP BY 學(xué)生姓名');
-- 4. 預(yù)處理并執(zhí)行動(dòng)態(tài)SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
執(zhí)行結(jié)果：無(wú)論科目如何變化，這段代碼都會(huì)自動(dòng)生成一個(gè)將所有科目作為列名的透視表。
| 學(xué)生姓名 | 語(yǔ)文 | 數(shù)學(xué) | 英語(yǔ) | （其他任何科目...） |
| :------- | :--- | :--- | :--- | :------------------ |
| 張三 | 90 | 85 | 92 | ... |
| 李四 | 95 | 70 | 88 | ... |
| 方法 | 適用場(chǎng)景 | 優(yōu)點(diǎn) | 缺點(diǎn) |
| :----------------- | :--------------------------------------------- | :--------------------------- | :----------------------------- |
| CASE WHEN/IF | 要轉(zhuǎn)換的列值是固定的、已知的（如科目固定） | 簡(jiǎn)單、直觀、性能好 | 無(wú)法自動(dòng)適應(yīng)列值的變化 |
| GROUP_CONCAT
| 動(dòng)態(tài)SQL | 要轉(zhuǎn)換的列值是動(dòng)態(tài)的、不確定的（如科目可變） | 全自動(dòng)，一勞永逸 | 編寫復(fù)雜，需要用到存儲(chǔ)過程和預(yù)處理 |
核心要點(diǎn)：
CASE WHEN/IF + MAX + GROUP BY。另外搭配便捷的MYSQL備份工具，可定時(shí)備份、異地備份，MYSQL導(dǎo)出導(dǎo)入。可本地連接LINUX里的MYSQL，簡(jiǎn)單便捷�？梢源蟠蟮靥岣吖ぷ餍枢�。
