declare @sql varchar(8000)
set @sql=''

declare @i int;set @i=5
select @sql=@sql+'sum(case when 分数>='+ltrim(number) +' and 分数<'+ltrim(number+@i)+ ' then 1 else 0 end) as ''['+ltrim(number)+'-'+ltrim(number+@i)+']'', '
 from master..spt_values
where type='p' and number%@i=0
and number between 0 and 95

select 'select '+left(@sql,len(@sql)-1)+' from tablename'

------解决方案--------------------

SQL code
declare @sql varchar(8000)
declare @i int
set @sql=''
set @i=10

select @sql=@sql+'sum(case when 分数>='+ltrim(@i*number) +' and 分数<'+ltrim(@i*number+@i)+ ' then 1 else 0 end) as ''['+ltrim(@i*number)+'-'+ltrim(@i*number+@i)+']'', '
 from master..spt_values
where type='p' and @i*number<100


select @sql='select '+stuff(@sql,len(@sql),1,'')+' from tb'
print(@sql)

------解决方案--------------------

SQL code

create table tb(score int)
insert into tb
select 6 union all
select 11 union all
select 56 union all
select 37 union all
select 34 union all
select 87 union all
select 14 union all
select 66 union all
select 92 union all
select 41 union all
select 25 union all
select 10
go

declare @sql varchar(8000)

declare @i int
set @i=5
select @sql=isnull(@sql+',','')+'sum(case when score>='+ltrim(number) +' and score<'+ltrim(number+@i)+ ' then 1 else 0 end) as ['+ltrim(number)+'-'+ltrim(number+@i)+']'
from master..spt_values
where type='p' and number%@i=0
and number between 0 and 95

select @sql = 'select '+@sql+' from tb'
exec(@sql)

drop table tb

/***********

0-5         5-10        10-15       15-20       20-25       25-30       30-35       35-40       40-45       45-50       50-55       55-60       60-65       65-70       70-75       75-80       80-85       85-90       90-95       95-100
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
0           1           3           0           0           1           1           1           1           0           0           1           0           1           0           0           0           1           1           0

(1 行受影响)

------解决方案--------------------

探讨

还有没有别的法，分数段太多的话，8000字符不够。总分高中是750分的时候，初中是950分。


------解决方案--------------------

SQL code

create table tb(score int)
insert into tb
select 6 union all
select 11 union all
select 56 union all
select 37 union all
select 34 union all
select 87 union all
select 14 union all
select 66 union all
select 92 union all
select 41 union all
select 25 union all
select 950
go

declare @str1 varchar(8000)
declare @str2 varchar(8000)

declare @i int
set @i=5
select @str1=isnull(@str1+',','')+'sum(case when score>='+ltrim(number) +' and score<'+ltrim(number+@i)+ ' then 1 else 0 end) as ['+ltrim(number)+'-'+ltrim(number+@i)+']'
from master..spt_values
where type='p' and number%@i=0
and number between 0 and 550

select @str2=isnull(@str2+',','')+'sum(case when score>='+ltrim(number) +' and score<'