您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 行轉列與分級輸出實戰：CASE WHEN 聚合、子查詢與動態存儲過程三種方案對比

編輯：MySQL 綜合教學

場景描述

學生成績表 stdscore(sname, cname, score)，主鍵 (sname, cname)。課程動態增減。需輸出：每生一行，課程為列，值為分數/等級。

-- 原始數據
sname | cname  | score
------|--------|------
Alice | Java   | 85
Alice | MySQL  | 90
Bob   | Java   | 70
Bob   | MySQL  | 60

目標 (行轉列)：

sname | Java | MySQL
------|------|------
Alice | 85   | 90
Bob   | 70   | 60

方案一：CASE WHEN + 聚合函數 (最常用、性能最優)

SELECT
  sname,
  MAX(CASE WHEN cname = 'Java'  THEN score ELSE 0 END) AS Java,
  MAX(CASE WHEN cname = 'MySQL' THEN score ELSE 0 END) AS MySQL
FROM stdscore
GROUP BY sname;

原理：GROUP BY 縮列，CASE 篩選列值，MAX/SUM 聚合 (每組僅一行非零值)。
NULL 處理：用 ELSE 0 或 COALESCE 避免無記錄課程顯示 NULL。
優點：單掃描、無連接、優化器友好、靜態 SQL 易維護。

方案二：子查詢/相關子查詢 (直觀但效率低)

SELECT DISTINCT a.sname,
  (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.cname='Java')  AS Java,
  (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.cname='MySQL') AS MySQL
FROM stdscore a;

缺點：每行每課程執行一次子查詢，N×M 次索引查找，大數據集極慢。
適用：極小數據集、臨時調試、課程極少且固定。

方案三：動態存儲過程 (課程動態增減自適應)

DELIMITER //
DROP PROCEDURE IF EXISTS sp_pivot_scores //
CREATE PROCEDURE sp_pivot_scores()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_cname VARCHAR(50);
  DECLARE v_sql TEXT DEFAULT 'SELECT sname';
  DECLARE cur CURSOR FOR SELECT DISTINCT cname FROM stdscore;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_cname;
    IF done THEN LEAVE read_loop; END IF;
    -- 動態拼接 CASE WHEN 列
    SET v_sql = CONCAT(v_sql, ', MAX(CASE WHEN cname = ', QUOTE(v_cname),
                       ' THEN score ELSE 0 END) AS ', QUOTE(v_cname));
  END LOOP;
  CLOSE cur;

  SET v_sql = CONCAT(v_sql, ' FROM stdscore GROUP BY sname');
  -- 調試: SELECT v_sql;
  PREPARE stmt FROM v_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 調用
CALL sp_pivot_scores();

核心優勢：新增課程無需修改代碼，自動擴展列。
風險：動態 SQL 難調試、注入風險 (用 QUOTE() 防禦)、權限要求高、執行計劃不固定。

進階：分級輸出 (分數→等級)

規則：>平均+20「優秀」、>平均+10「良好」、≥平均「普通」、否則「較差」。

靜態 SQL (方案一擴展)

SELECT
  sname,
  MAX(CASE WHEN cname='Java' THEN
    CASE
      WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname='Java') > 20 THEN '優秀'
      WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname='Java') > 10 THEN '良好'
      WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname='Java') >= 0 THEN '普通'
      ELSE '較差'
    END
  END) AS Java,
  MAX(CASE WHEN cname='MySQL' THEN
    CASE
      WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname='MySQL') > 20 THEN '優秀'
      WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname='MySQL') > 10 THEN '良好'
      WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname='MySQL') >= 0 THEN '普通'
      ELSE '較差'
    END
  END) AS MySQL
FROM stdscore
GROUP BY sname;

動態存儲過程 (方案三擴展)

SET v_sql = CONCAT(v_sql, ', MAX(CASE WHEN cname = ', QUOTE(v_cname),
                   ' THEN CASE
                     WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname=', QUOTE(v_cname), ') > 20 THEN \'優秀\'
                     WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname=', QUOTE(v_cname), ') > 10 THEN \'良好\'
                     WHEN score - (SELECT AVG(score) FROM stdscore WHERE cname=', QUOTE(v_cname), ') >= 0 THEN \'普通\'
                     ELSE \'較差\' END
                   END) AS ', QUOTE(v_cname));

方案對比總結

維度	CASE WHEN 聚合	子查詢	動態存儲過程
性能	⭐⭐⭐⭐⭐ (單掃描)	⭐ (N×M 查找)	⭐⭐⭐ (同方案一，但動態解析)
維護性	課程變更需改 SQL	課程變更需改 SQL	⭐⭐⭐⭐⭐ (自適應)
調試難度	低	低	高 (動態 SQL)
適用場景	課程相對固定、高性能要求	極小數據、臨時查詢	課程頻繁動態增減、可接受維護成本