• 主页

# 求SQL语句：计算每个成绩段的学生人数

goodsong 2004-09-11 07:56:38
table1
number name score
100001 aaaa 10
100002 aaab 20
100003 aaac 15
... ... ...

select count(*)
from table1
where score>=0 and score <5

select count(*)
from table1
where score>=5 and score <10

...全文
270 点赞 收藏 10

10 条回复

goodsong 2004年09月11日

ouyld 2004年09月11日

--个人觉得这样统计吧,你的分类段是固定的,这样的写法,效率上应该是比冒牌的高
--如果某个分数段没有人,也会把该分数段显示出来.

select 成绩段=b.成绩段,人数=count(a.score)
from table1 a right join(
select 成绩段='0--5',a=0,b=5
union all select '5--10',5,10
union all select '10--15',10,15
union all select '15--20',15,20
union all select '20--25',20,25
union all select '25--30',25,30
union all select '30--35',30,35
union all select '35--40',35,40
union all select '40--45',40,45
union all select '45--50',45,50
union all select '50--55',50,55
union all select '55--60',55,60
union all select '60--65',60,65
union all select '65--70',65,70
union all select '70--75',70,75
union all select '75--80',75,80
union all select '80--85',80,85
union all select '85--90',85,90
union all select '90--95',90,95
union all select '95--100',95,101
)b on a.score>=b.a and a.score<b.b
group by b.成绩段

zicxc 2004年09月11日

venket 2004年09月11日

zicxc 2004年09月11日

select case when score=100 then '95--100' else cast(cast(score as int)/5*5 as varchar(10))+'--'+cast((cast(score as int)/5+1)*5 as varchar(10)) end as 分数段,
count(*) as 人数
from table1
group by case when score=100 then '95--100' else cast(cast(score as int)/5*5 as varchar(10))+'--'+cast((cast(score as int)/5+1)*5 as varchar(10)) end
order by min(score)

zicxc 2004年09月11日

select cast(cast(score as int)/5*5 as varchar(10))+'--'+cast((cast(score as int)/5+1)*5 as varchar(10)) as 分数段,
count(*) as 人数
from table1
group by cast(cast(score as int)/5*5 as varchar(10))+'--'+cast((cast(score as int)/5+1)*5 as varchar(10))

zicxc 2004年09月11日
select cast(cast(score as int)/5*5 as varchar(10))+'--'+cast((cast(score as int)/5+1)*5 as varchar(10)) as 分数段,
count(*) as 人数
from table1
group by cast(score as int)/5

1.1w+

6.8w+

MS-SQL Server 应用实例