分段查询统计的问题
数据表有两个字段，
id count
** ***
要求根据count的大小统计id的个数
如
10以下 **
10-100 **
100 ***
1000以上 **
如果某个区段没有，则不显示
请问SQL语句怎么写？
[解决办法]
declare @t table(id int,[count] int)
insert @t
select 1,10 union all
select 2,11 union all
select 3,101 union all
select 4,102 union all
select 5,103
SELECT * FROM (
select '10以下 ' as 范围,count(id) as 个数 from @t where [count] < 10
union all
select '10-100 ' as 范围,count(id) as 个数 from @t where [count] between 10 and 100
union all
select '100-1000 ' as 范围,count(id) as 个数 from @t where [count] > 100 and [count] <= 1000
union all
select '1000以上 ' as 范围,count(id) as 个数 from @t where [count] > 1000
) AS t WHERE 个数 > 0
/*结果
范围 个数
-------- -----------
10-100 2
100-1000 3
*/
[解决办法]
declare @t table(id int,[count] int)
insert @t
select 1,1 union all
select 2,11 union all
select 3,101 union all
select 4,102 union all
select 5,1010 union all
select 6,100 union all
select 7,103
select case when [count] <10 then '1: 10以下 '
when [count]> =10 and [count] <100 then '2: 10-100 '
when [count]> =100 and [count] <1000 then '3: 100-1000 '
else '4: 1000以上 ' end [range],count(id) [count]
from @t
group by case when [count] <10 then '1: 10以下 '
when [count]> =10 and [count] <100 then '2: 10-100 '
when [count]> =100 and [count] <1000 then '3: 100-1000 '
else '4: 1000以上 ' end
