比如表数据如下:
ID NUM
1 22
2 123
3 234
4 111
5 222
6 256
7 44要求输出num的统计,同时输出0-100,100-200,200-300范围的num各有多少即
0-100 100-200 200-300
2 2 3
ID NUM
1 22
2 123
3 234
4 111
5 222
6 256
7 44要求输出num的统计,同时输出0-100,100-200,200-300范围的num各有多少即
0-100 100-200 200-300
2 2 3
[0-100] = (select count(*) from tb where num between 0 and 100),
[100-200] = (select count(*) from tb where num between 100 and 200),
[200-300] = (select count(*) from tb where num between 200 and 300)
sum(case when num between 100 and 200 then 1 else 0 end) as '100-200',
sum(case when num between 200 and 300 then 1 else 0 end) as '200-300'
from 表
insert into tb values(1 , 22 )
insert into tb values(2 , 123)
insert into tb values(3 , 234)
insert into tb values(4 , 111)
insert into tb values(5 , 222)
insert into tb values(6 , 256)
insert into tb values(7 , 44 )
goselect
[0-100] = (select count(*) from tb where num between 0 and 100),
[100-200] = (select count(*) from tb where num between 100 and 200),
[200-300] = (select count(*) from tb where num between 200 and 300)
drop table tb/*
0-100 100-200 200-300
----------- ----------- -----------
2 2 3(所影响的行数为 1 行)
*/
insert into @tb select 1,22
insert into @tb select 2,123
insert into @tb select 3,234
insert into @tb select 4,111
insert into @tb select 5,222
insert into @tb select 6,256
insert into @tb select 7,44select
sum(case when num between 0 and 100 then 1 else 0 end ) as '0-100',
sum(case when num between 100 and 200 then 1 else 0 end ) as '100-200',
sum(case when num between 200 and 300 then 1 else 0 end ) as '200-300'
from @tb0-100 100-200 200-300
2 2 3
case num/100
when 0 then '0-100'
when 1 then '100-200'
when 2 then '200-300'
else '300-'
end
,count(*)
from #temp
group by num / 100