select 类别,
[0-9]=sum(case (cast(right(号码,2) as int)+1)/10 when 0 then 1 else 0 end),
[10-19]=sum(case (cast(right(号码,2) as int)+1)/10 when 1 then 1 else 0 end),
...
[90-99]=sum(case (cast(right(号码,2) as int)+1)/10 when 9 or 10 then 1 else 0 end)
from t
group by 类别
[0-9]=sum(case (cast(right(号码,2) as int)+1)/10 when 0 then 1 else 0 end),
[10-19]=sum(case (cast(right(号码,2) as int)+1)/10 when 1 then 1 else 0 end),
...
[90-99]=sum(case (cast(right(号码,2) as int)+1)/10 when 9 or 10 then 1 else 0 end)
from t
group by 类别
[0-9]=sum(case when right(号码,2) between 0 and 9 then 1 else 0 end),
[10-19]=sum(case when right(号码,2) between 10 and 19 then 1 else 0 end),
[20-29]=sum(case when right(号码,2) between 20 and 29 then 1 else 0 end),
[30-39]=sum(case when right(号码,2) between 30 and 39 then 1 else 0 end),
...........
[90-99]=sum(case when right(号码,2) between 90 and 99 then 1 else 0 end)
from 表 group by 类别
[0-9]=sum(case (cast(right(号码,2) as int)+1)/10 when 0 then 1 else 0 end),
[10-19]=sum(case (cast(right(号码,2) as int)+1)/10 when 1 then 1 else 0 end),
...
[90-99]=sum(case when (cast(right(号码,2) as int)+1)/10=9
or (cast(right(号码,2) as int)+1)/10=10
then 1 else 0 end)
from t
group by 类别
go
insert into #t select 123,'a' union all select 345,'b' union all
select 566,'a' union all select 234,'b' union all select 234,'b' union all
select 678,'b' union all select 876,'a' union all select 443,'b' union all
select 234,'c'
go
select * from #t
go
select 类别,[0-9]=sum(case when 号码%100 between 0 and 9 then 1 else 0 end),
[10-19]=sum(case when 号码%100 between 10 and 19 then 1 else 0 end) ,
[20-29]=sum(case when 号码%100 between 20 and 29 then 1 else 0 end),
[30-39]=sum(case when 号码%100 between 30 and 39 then 1 else 0 end),
[40-49]=sum(case when 号码%100 between 40 and 49 then 1 else 0 end),
[50-59]=sum(case when 号码%100 between 50 and 59 then 1 else 0 end),
[60-69]=sum(case when 号码%100 between 60 and 69 then 1 else 0 end),
[70-79]=sum(case when 号码%100 between 70 and 79 then 1 else 0 end),
[80-89]=sum(case when 号码%100 between 80 and 89 then 1 else 0 end),
[90-99]=sum(case when 号码%100 between 90 and 99 then 1 else 0 end)
from #t group by 类别
----------- ----
123 a
345 b
566 a
234 b
234 b
678 b
876 a
443 b
234 c(所影响的行数为 9 行)
结果:
类别 0-9 10-19 20-29 30-39 40-49 50-59 60-69 70-79 80-89 90-99
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a 0 0 1 0 0 0 1 1 0 0
b 0 0 0 2 2 0 0 1 0 0
c 0 0 0 1 0 0 0 0 0 0(所影响的行数为 3 行)
[0-9]=sum(case when right(号码,2) between 0 and 9 then 1 else 0 end),
[10-19]=sum(case when right(号码,2) between 10 and 19 then 1 else 0 end),
[20-29]=sum(case when right(号码,2) between 20 and 29 then 1 else 0 end),
[30-39]=sum(case when right(号码,2) between 30 and 39 then 1 else 0 end),
...........
[90-99]=sum(case when right(号码,2) between 90 and 99 then 1 else 0 end)
from 表 group by 类别
你要明白group by 的意思就行了 看看书吧 甚于sum 是一个函数...