MySQL 動態行轉列實戰：存儲過程 + PREPARE 實現課程成績橫表輸出

以「學生-課程-成績」三表為例，展示如何用 GROUP_CONCAT 動態生成列、配合 PREPARE/EXECUTE 實現動態行轉列，並封裝為存儲過程支援條件參數，解決 MyBatis 等 ORM 無法直接執行動態 SQL 的難題。

資料模型

• student(stuid, stunm)
• courses(courseno, coursenm)
• score(stuid, courseno, scores)

靜態行轉列（基準）

SELECT st.stuid, st.stunm,
  MAX(CASE c.coursenm WHEN '大學語文' THEN s.scores ELSE 0 END) AS '大學語文',
  MAX(CASE c.coursenm WHEN '線性代數' THEN IFNULL(s.scores,0) ELSE 0 END) AS '線性代數',
  ...
FROM student st
LEFT JOIN score s ON st.stuid = s.stuid
LEFT JOIN courses c ON c.courseno = s.courseno
GROUP BY st.stuid;

動態生成列清單

SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'MAX(IF(c.coursenm = ''', c.coursenm, ''', s.scores, 0)) AS ''', c.coursenm, ''''
)) FROM courses c;

輸出：MAX(IF(c.coursenm = '大學語文', s.scores, 0)) AS '大學語文', MAX(IF(c.coursenm = '線性代數', s.scores, 0)) AS '線性代數', ...

動態 SQL 組裝與執行

SET @sql = NULL;
SELECT GROUP_CONCAT(...) INTO @sql FROM courses c;
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql,
  ' FROM student st LEFT JOIN score s ON st.stuid = s.stuid LEFT JOIN courses c ON c.courseno = s.courseno GROUP BY st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

封裝為存儲過程支援條件查詢

DELIMITER $$
DROP PROCEDURE IF EXISTS SP_QueryData;
CREATE PROCEDURE SP_QueryData(IN p_stuid VARCHAR(16))
READS SQL DATA
BEGIN
  SET @sql = NULL;
  SET @stuid = NULL;
  SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'MAX(IF(c.coursenm = ''', c.coursenm, ''', s.scores, 0)) AS ''', c.coursenm, ''''
  )) INTO @sql FROM courses c;
  SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql,
    ' FROM student st LEFT JOIN score s ON st.stuid = s.stuid LEFT JOIN courses c ON c.courseno = s.courseno');
  IF p_stuid IS NOT NULL AND p_stuid <> '' THEN
    SET @stuid = p_stuid;
    SET @sql = CONCAT(@sql, ' WHERE st.stuid = ''', @stuid, '''');
  END IF;
  SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

調用示例

CALL SP_QueryData('1001');  -- 單學生
CALL SP_QueryData('');       -- 全部學生

關鍵避坑指南

• IF 語法：MySQL 存儲過程中 IF condition THEN ... END IF; 無括號、無大括號。
• 變量區分：@sql 用戶變量跨語句傳遞，p_stuid 為輸入參數。
• MyBatis 整合：Mapper 僅需 {call SP_QueryData(#{stuid})}，避開 XML 動態 SQL 限制。

此模式適用於「列不固定、需動態生成、且需條件過濾」的報表查詢場景。