Mysql 学生成绩等级判断 列变行 —— 个人学习记录

思路：

step1：CASE ... WHEN ... 列变行

step2：相同name整合成一行。group by + 聚合函数

SELECT
    name,
    max(CASE
        WHEN kecheng='语文' AND fenshu>=80 THEN '优秀'
        WHEN kecheng='语文' AND fenshu>=60 THEN '及格'
        WHEN kecheng='语文' AND fenshu<60 THEN '不及格'
    END) as `语文`,
    max(CASE
        WHEN kecheng='数学' AND fenshu>=80 THEN '优秀'
        WHEN kecheng='数学' AND fenshu>=60 THEN '及格'
        WHEN kecheng='数学' AND fenshu<60 THEN '不及格'
    END) as `数学`,
    max(CASE
        WHEN kecheng='英语' AND fenshu>=80 THEN '优秀'
        WHEN kecheng='英语' AND fenshu>=60 THEN '及格'
        WHEN kecheng='英语' AND fenshu<60 THEN '不及格'
    END) AS `英语`
FROM t_score
GROUP BY name;