Question

SQL怎么查询某个班级的不同成绩段的总人数？

SQL怎么查询某个班级的不同成绩段的总人数？
count(score),但是这个score是分段的。。

Answer 1

--建立测试表
create table score (score_id int identity(1,1)
,stu_id int
,class_id int
,course_id int
,score float);
--插入测试数据
insert into score (stu_id,class_id,course_id,score)
select 1,1,1,89
union
select 2,1,1,59
union
select 3,1,1,64
union
select 4,1,1,82
union
select 5,1,1,57
union
select 6,1,1,92
union
select 7,1,1,85
union
select 8,1,1,96
union
select 9,1,1,75 ;
--检验测试数据
select * from score;

--1.如果以10分1档 如下
select count(1) ct,round(score-5,-1) tt
from score
where class_id=1 and course_id=1
group by round(score-5,-1);

--2.可以在when条件增减粒度
select count(1) ct,ss from
(
select case when score <60 then '不及格'
when score <70 and score >60 then '60'
when score <80 and score >70 then '70'
when score <90 and score >80 then '80'
when score <100 and score >90 then '90'
else '100'
end
ss from score
where class_id=1 and course_id=1
) tt group by ss

还有楼上方法经测试效率比较好。
select sum(case when score <60 then 1 else 0 end) '不及格'
,sum(case when score <70 and score >60 then 1 else 0 end) '60'
, sum(case when score <80 and score >70 then 1 else 0 end) '70'
, sum(case when score <90 and score >80 then 1 else 0 end) '80'
, sum(case when score <100 and score >90 then 1 else 0 end) '90'
, sum(case when score =100 then 1 else 0 end) '100'
from score

Answer 2

select sum(case score when .. then 1 else 0 end) 分数段1,....

Answer 3

where score between ....