求一条SQL语句，想了很久了，没有答案！

[seu31199113]

原始表：
分数 人数
95 1
92 10
91 5
89 2
86 3
84 20
80 6
79 5
……

问题是：以5分为一个分数段，统计各分数段的人数

期望的结果：

分数段 合计人数
100~95 1
95~90 15
90~85 5
85~80 26
……
以此类推


谢谢各位帮忙!

[FromNoWhere]

我贴一个吧.参考.
drop table Original
create table Original( nMark int, nTotal int)
insert Original select 95,1 union
select 92,10 union
select 91,5 union
select 89,2 union
select 86,3 union
select 84,20 union
select 79,5
select * from Original
declare @nMax int
declare @nMin int
declare @nNum int
declare @nTmp int
set @nMax=100
set @nMin=0
set @nNum=@nMax
create table #tmp (nMax int, nMin int, nTotal int)
while (@nNum!=0)
begin
set @nTmp=0
select @nTmp=sum(nTotal) from Original where nMark<=
@nNum and nMark>@nNum-5
print 'nTmp'
print @nTmp
insert into #tmp values(@nNum,@nNum-5,@nTmp)
set @nNum=@nNum-5
end

select * from #tmp
drop table #tmp

[zanyzyg]

一条语句就可以搞定了：

select * into #table from
(
select 95 as score, 1 as num union
select 92 , 10 union
select 91 , 5 union
select 89 , 2 union
select 86 , 3 union
select 84 , 20 union
select 80 , 6 union
select 79 , 5
)aaa


select * from #table

select convert(varchar(2),score/5*5)+'-'+convert(varchar(3),score/5*5+5) as 'aaa',sum(num) as 'bbb'
from #table
group by convert(varchar(2),score/5*5)+'-'+convert(varchar(3),score/5*5+5)

drop table #table

--结果
75-80 5
80-85 26
85-90 5
90-95 15
95-100 1

[rouqu]

create function myfun1(@par int)
returns varchar(10)
as
begin
declare @can varchar(10)
if (par%5 !=0 )
@can = cast(floor(@par/5) as varchar(10)) + cast((floor(@par/5)+5) as varchar(10))
else
@can = cast((floor(@par/5)-5) as varchar(10)) + cast(floor(@par/5) as varchar(10))
return(@can)
end

select myfun1(分数) as 分数段,人数 into #tep1 from 原始表

select 分数段，sum（人数）as 合计人数 from #tep1 group by 分数段

[rivery]

--说明：没有分数在的分数段不会出现在结果中，如需要，则使用辅助函数输出分数段列表与原表左链接。
--测试表
declare @t table(fen int ,ren int)
insert into @t
select 95 , 1 union
select 92 , 10 union
select 91 , 5 union
select 89 , 2 union
select 86 , 3 union
select 84 , 20 union
select 80 , 6 union
select 79 , 5
--语句
select convert(varchar,(1+step)*5)+'-'+convert(varchar,(step)*5),cnt
from (select (fen/5) as step,sum(ren) as cnt
from @t
group by fen/5) new
order by step desc
--结果
/*
100-95 1
95-90 15
90-85 5
85-80 26
80-75 5

*/

[iwl]

DEclare @tb table(score int ,peono int)

insert into @tb select 95, 1
union select 92, 10
union select 91 , 5
union select 89 , 2
union select 86 , 3
union select 84 , 20
union select 80 , 6
union select 79 , 5

select cast(min(score)/5*5 as varchar(3))+'~'+cast((min(score)/5+1)*5 as varchar(3)),sum(peono) from @tb group by score/5 order by score/5 desc

[rouqu]

建表：
create table original (
fenshu tinyint not null,
renshu tinyint not null
)

插入测试值：
insert into original
select 95, 1 union all
select 92,10 union all
select 91,5 union all
select 89,2 union all
select 86,3 union all
select 84,20 union all
select 80,6 union all
select 79,5

建函数：
create function myfun1(@par int)
returns varchar(10)
as
begin
declare @can varchar(10)
if (@par%5 <> 0 )
set @can = cast(floor(@par/5)*5 as varchar(10)) + '~'+cast((floor(@par/5)*5+5) as varchar(10))
else
set @can = cast((floor(@par/5)*5-5) as varchar(10)) + '~'+cast(floor(@par/5)*5 as varchar(10))
return(@can)
end

测试：

select dbo.myfun1(fenshu) as fenshuduan,renshu into #tep1 from original

select fenshuduan,sum(renshu) as zongrenshu from #tep1 group by fenshuduan

drop table #tep1

结果：
zongfenshu zongrenshu
---------- -----------
75~80 11
80~85 20
85~90 5
90~95 16

(4 row(s) affected)

[rouqu]

晕 我的好麻烦