Mysql case when 用法

-- 21 查询出每门课程的及格人数和不及格人数

SELECT
    课程号,
    COUNT(CASE WHEN 成绩 >= 60 THEN 1 END ‘别名’) AS 及格人数,
    COUNT(CASE WHEN 成绩 < 60 OR 成绩 IS NULL THEN 1 END ‘别名’) AS 不及格人数
FROM
    score
GROUP BY
    课程号;

-- 15 查询各科成绩前两名的记录

SELECT *
FROM (
    SELECT
        *,
        RANK() OVER (PARTITION BY 课程号 ORDER BY 成绩 DESC) AS 排名
    FROM
        score
) AS ranked_scores
WHERE 排名 <= 2;