问题:
有一个分数表
id classid,score
1 01 120
2 01 128
3 02 98
4 04 134
5 04 78
现在要统计 各班score >120,和大于90分的人数
达到如下效果
classid >120 >90
01 10 29
02 9 32
03 0 20
答案:
select classid, sum ( case when score > 120 then 1 else 0 end ) as [ >120 ] ,
sum ( case when score > 90 and score <=120 then 1 else 0 end ) as [ >90 ]
from tb
group by classid