MySQL 行列轉換實戰：兩種高效 SQL 方案詳解

業務場景

將垂直存儲的課程成績數據轉換為橫向展示格式，便於報表生成和前端展示。

原始數據結構

目標結果

方案一：條件聚合（推薦）

使用 CASE WHEN 配合聚合函數，單次掃描完成轉換，性能最優：

SELECT 
    name,
    SUM(CASE WHEN course = 'Java' THEN grade END) AS Java,
    SUM(CASE WHEN course = 'C++' THEN grade END) AS `C++`,
    SUM(CASE WHEN course = 'C#' THEN grade END) AS `C#`
FROM test
GROUP BY name;

優點：執行計劃簡單、索引友好、易於擴展新課程列、兼容所有 MySQL 版本。
注意：課程名稱含特殊符號（如 C++、C#）需用反引號包裹別名。

方案二：子查詢轉換

通過關聯子查詢逐列獲取數據，邏輯直觀但性能較弱：

SELECT DISTINCT 
    c.`name` AS name,
    (SELECT grade FROM test WHERE name = c.`name` AND course = 'Java') AS Java,
    (SELECT grade FROM test WHERE name = c.`name` AND course = 'C++') AS `C++`,
    (SELECT grade FROM test WHERE name = c.`name` AND course = 'C#') AS `C#`
FROM test c;

缺點：每行執行多次子查詢，數據量大時性能顯著下降；若同一學生同一課程有多條記錄會報錯。

性能對比與選型建議

進階技巧：動態列生成

若課程類型不固定，可使用預處理語句動態構建 SQL：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(CASE WHEN course = "', course, '" THEN grade END) AS `', course, '`')
) INTO @sql FROM test;

SET @sql = CONCAT('SELECT name, ', @sql, ' FROM test GROUP BY name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

總結

MySQL 行列轉換首選 條件聚合方案，兼顧性能與維護性。子查詢方案僅適合數據量極小或一次性分析場景。實際項目中建議結合業務需求，對固定列使用靜態 SQL，動態列採用預處理動態生成。