在 table A 的字段及记录如下编号(id) 姓名(name) 成绩(score)
001 aaaa 90
002 baaa 81
003 ataa 76
004 aa4a 69
005 aada 78
006 ajaa 92
007 aava 95
008 aasa 63score 在60--69 为 d,70--79为c,80--89为b,90-100为a,用一条sql语句查询出 a,b,c,d四种分数等级下各有多少人。
请高手指点
001 aaaa 90
002 baaa 81
003 ataa 76
004 aa4a 69
005 aada 78
006 ajaa 92
007 aava 95
008 aasa 63score 在60--69 为 d,70--79为c,80--89为b,90-100为a,用一条sql语句查询出 a,b,c,d四种分数等级下各有多少人。
请高手指点
count(case when score between 70 and 79 then 1 end),
count(case when score between 80 and 89 then 1 end),
count(case when score >= 90 then 1 end)
from a;
from a
group by floor(score/10)
select sum(case when score between 60 and 69 then 1 end),
sum(case when score between 70 and 79 then 1 end),
sum(case when score between 80 and 89 then 1 end),
sum(case when score between 90 and 100 then 1 end)
from a;
用sum和count有什么不同呢
count(case when score between 70 and 79 then 1 end),
count(case when score between 80 and 89 then 1 end),
count(case when score >= 90 then 1 end)
from a;
sum( case when chengji>=80 and chengji<90 then 1 else 0 end) "b",
sum( case when chengji>=70 and chengji<80 then 1 else 0 end ) "c",
sum( case when chengji>=60 and chengji< 70 then 1 else 0 end) "d"
from a;