【2020090402】case when。。。then。。。end 的用法

-- 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
        sc.cid
        ,c.cname
        ,SUM(CASE WHEN score >= 90 AND score <= 100 THEN 1 ELSE 0 END)/COUNT(score) AS '[85-100]'
        ,SUM(CASE WHEN score >= 70 AND score <85 THEN 1 ELSE 0 END)/COUNT(score) AS '[70-85)'
        ,SUM(CASE WHEN score >= 60 AND score <70 THEN 1 ELSE 0 END)/COUNT(score) AS '[60-70)'
        ,SUM(CASE WHEN score >= 0 AND score <60 THEN 1 ELSE 0 END)/COUNT(score) AS '[0-60)'
FROM sc
LEFT JOIN course AS c
ON sc.cid=c.cid
GROUP BY cid;

运行结果：