23、

-- 23、用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称(重点和18题类似)
SELECT sc.c_id,co.c_name,
SUM(CASE WHEN sc.s_score<=100 and sc.s_score>85 then 1 ELSE 0 END)'[100-85)',
SUM(CASE WHEN sc.s_score<=85 and sc.s_score>70 then 1 ELSE 0 END)'[85-70)',
COUNT(CASE WHEN sc.s_score<=70 and sc.s_score>60 then 521 ELSE NULL END)'[70-60)',
SUM(CASE WHEN sc.s_score<=60 then 1 ELSE 0 END)'[<60]'
FROM
score as sc
INNER JOIN
course as co ON sc.c_id=co.c_id
GROUP BY sc.c_id,co.c_name


重点注意SUM 跟COUNT 的计数方式