SQL面试50题-23

链接：https://zhuanlan.zhihu.com/p/43289968

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

SELECT s.c_id,c.c_name,
SUM(CASE WHEN s.s_score < 60 THEN 1 ELSE 0 END) AS "[<60]",
SUM(CASE WHEN s.s_score < 70 AND s.s_score >= 60 THEN 1 ELSE 0 END) AS "[70-60]",
SUM(CASE WHEN s.s_score < 85 AND s.s_score >= 70 THEN 1 ELSE 0 END) AS "[85-70]",
SUM(CASE WHEN s.s_score < 100 AND s.s_score >= 85 THEN 1 ELSE 0 END) AS "[100-85]"
FROM Score s INNER JOIN Course c ON c.c_id = s.c_id
GROUP BY s.c_id,c.c_name;