如何用sql语句实现统计各分数段的个数
如果有这样的一个表
score
23
38
30
45
60
如何用sql语句实现统计各分数段的个数？
设定的分数段有：0~20、20~30、31~40、41~50、51~60等等如此。
那么上表通过SQL语句，会变成
分数段       个数
0~20             0
20~30           1
31~40           2
………………
请问各位，这种SQL语句应该怎么实现呢？


------解决方案--------------------
declare @t table (score int)
insert @t select 23
union all select 38
union all select 30
union all select 45
union all select 60

select cast(score/10*10 as varchar)+ '~ '+cast(score/10*10+10 as varchar),count(score) from @t group by score/10

---
20~30 1
30~40 2
40~50 1
60~70 1
------解决方案--------------------
笨办法:
declare @t table (score int)
insert @t select 23
union all select 38
union all select 30
union all select 45
union all select 60

select '0~20 ' 分数段,sum(case when score between 0 and 20 then 1 else 0 end) 个数
from @t
union all
select '21~30 ' 分数段,sum(case when score between 21 and 30 then 1 else 0 end) 个数
from @t
union all
select '31~40 ' 分数段,sum(case when score between 31 and 40 then 1 else 0 end) 个数
from @t
..............

--结果
分数段 个数
----- -----------
0~20 0
21~30 2
31~40 1

（所影响的行数为 3 行）


------解决方案--------------------
create table T(score int)
insert T select 23
union all select 38
union all select 30
union all select 45
union all select 60

select 分数段= '[0-20] ', 个数=(select count(*) from T where score between 0 and 20)
union all
select 分数段= '[21-30] ', 个数=(select count(*) from T where score between 21 and 30)
union all
select 分数段= '[31-40] ', 个数=(select count(*) from T where score between 31 and 40)
union all
select 分数段= '[41-50] ', 个数=(select count(*) from T where score between 41 and 50)
union all
select 分数段= '[51-60] ', 个数=(select count(*) from T where score between 51 and 60)

--result
分数段 个数
------- -----------
[0-20] 0
[21-30] 2
[31-40] 1
[41-50] 1
[51-60] 1

(5 row(s) affected)
------解决方案--------------------
declare @t table (score int)
insert @T select 23
union all select 38
union all select 30
union all select 45
union all select 60
select
score= case when score between 0 and 20 then '0-20 '
when score between 21 and 30 then '21-30 '
when score between 31 and 40 then '31-40 '
when score between 41 and 50 then '41-50 '
ELSE '60以上 'end,
记录=count(*)
from @T
group by case when score between 0 and 20 then '0-20 '
when score between 21 and 30 then '21-30 '
when score between 31 and 40 then '31-40 '
when score between 41 and 50 then '41-50 ' ELSE '60以上 ' end
（所影响的行数为 5 行）

score 记录
------ -----------
21-30 2
31-40 1
41-50 1
60以上 1

（所影响的行数为 4 行）