问个简单的分组问题,各位帮下忙
我现在一个表里有大量的数据,我想取出来的时候,把它分组。例:
字段 A
1231
321
23
12
501
123131
希望出来结果
个数
---------
500以上 3
0-100 2
100-500 1
大家指教下。谢谢。
[解决办法]
declare @t table(A int)
insert into @t
select 1231
union all select 321
union all select 23
union all select 12
union all select 501
union all select 123131
select sum(case when A> 500 then 1 else 0 end) as '500以上 ',
sum(case when A between 0 and 100 then 1 else 0 end) as '0-100 ',
sum(case when A between 100 and 500 then 1 else 0 end) as '0-100 '
from @t
[解决办法]
select 组,个数
from (
select '500以上 ' as 组,count(*) as 个数 from 表 where A> =500
union
select '0-100 ' as 组,count(*) as 个数 from 表 where A> =0 and A <100
union
select '100-500 ' as 组,count(*) as 个数 from 表 where A> =10 and A <500
) AS T
order by 个数 desc
[解决办法]
create table A(num bigint)
insert A select
1231 union all select
321 union all select
23 union all select
12 union all select
501 union all select
123131
select case when num> 500 then '500以上 ' when num> 100 and num <=500 then '100-500 ' when num <=100 then '0-100 ' end as area,count(*) from a group by case when num> 500 then '500以上 ' when num> 100 and num <=500 then '100-500 ' when num <=100 then '0-100 ' end
--drop table A