MySQL行轉列實戰：CASE WHEN + SUM 聚合實現動態樞紐查詢

業務場景：學生成績橫表展示

源表rate：uname(姓名)、object(科目)、score(分數)，縱向存儲。需轉為橫表：每行一學生，列為各科目分數。

核心SQL：條件聚合法

SELECT DISTINCT uname AS '姓名',
    SUM(CASE object WHEN 'chinese' THEN score END) AS '語文',
    SUM(CASE object WHEN 'math' THEN score END) AS '數學',
    SUM(CASE object WHEN 'physical' THEN score END) AS '物理'
FROM rate
GROUP BY uname;

原理解析

• CASE WHEN ... THEN score END：匹配科目返回分數，否則返回NULL
• SUM()：忽略NULL，僅累加目標科目分數
• GROUP BY uname：按學生分組，每組產生一行結果

進階變體

動態列（預備語句構建）

科目不固定時，先查詢SELECT DISTINCT object FROM rate，應用層拼接CASE分支，再PREPARE/EXECUTE。

MAX/MIN替代SUM

若每組每科目僅一條記錄，MAX(CASE...)語義更清晰，性能相近。

處理NULL顯示

COALESCE(SUM(CASE...), 0) AS '語文'  -- 無記錄顯示0而非NULL

性能優化建議

• 索引覆蓋：INDEX idx_uname_object_score (uname, object, score)實現Index Only Scan
• 大數據量分區：按uname哈希分區，並行聚合
• 物化視圖：高頻查詢創建SUMMARY TABLE定時刷新

條件聚合是MySQL行轉列標準、高效、可移植的最佳實踐，無需存儲過程或應用層循環。