MySQL 行轉列與分級輸出：CASE WHEN 結合條件聚合實現學員課程等級展示

需求場景

原始表：

SNAME  CNAME  SCORE
小李    JAVA   98
小李    mysql  78
小明    JAVA   88
小明    mysql  88

目標輸出：

sname  JAVA    MYSQL
小李    優秀    普通
小明    普通    普通

分級規則：>平均分+20→優秀，>平均分+10→良好，=平均分→普通，<平均分→一般，<平均分-10→較差。

解決思路

1. 先計算每門課的平均分（子查詢或 CTE）。
2. 主查詢按學員分組，用 CASE WHEN 對每門課分數分級。
3. 利用條件聚合 MAX(CASE...) 實現行轉列。

完整 SQL

WITH avg_scores AS (
    SELECT CNAME, AVG(SCORE) AS avg_score
    FROM scores
    GROUP BY CNAME
)
SELECT 
    s.SNAME,
    MAX(CASE WHEN s.CNAME = 'JAVA' THEN
        CASE 
            WHEN s.SCORE > a.avg_score + 20 THEN '優秀'
            WHEN s.SCORE > a.avg_score + 10 THEN '良好'
            WHEN s.SCORE = a.avg_score THEN '普通'
            WHEN s.SCORE < a.avg_score - 10 THEN '較差'
            ELSE '一般'
        END
    END) AS JAVA,
    MAX(CASE WHEN s.CNAME = 'mysql' THEN
        CASE 
            WHEN s.SCORE > a.avg_score + 20 THEN '優秀'
            WHEN s.SCORE > a.avg_score + 10 THEN '良好'
            WHEN s.SCORE = a.avg_score THEN '普通'
            WHEN s.SCORE < a.avg_score - 10 THEN '較差'
            ELSE '一般'
        END
    END) AS MYSQL
FROM scores s
JOIN avg_scores a ON s.CNAME = a.CNAME
GROUP BY s.SNAME;

關鍵點解析

• WITH 子句（MySQL 8.0+）計算各課平均分，舊版本可用子查詢或臨時表。
• 外層 MAX(CASE...) 配合 GROUP BY SNAME 將多行壓縮為一行多列。
• 內層 CASE 實現分級邏輯，條件順序自高至低，ELSE 兜底。

兼容性提示

MySQL 5.7 及以下無 WITH，改用派生表：

SELECT ... FROM scores s
JOIN (SELECT CNAME, AVG(SCORE) avg_score FROM scores GROUP BY CNAME) a
  ON s.CNAME = a.CNAME
GROUP BY s.SNAME;