SQL : 练习题-17题

统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
有时候觉得自己真是死脑筋。group by以后的查询结果无法使用别名，所以不要想着先单表group by计算出结果再从第二张表里添上课程信息，而应该先将两张表join在一起得到所有想要的属性再对这张总表进行统计计算。这里就不算百分比了，道理相同。
注意一下，用case when 返回1 以后的统计不是用count而是sum

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;