sql server 2008 分组查询

[kangwei_ch]

各位，我有一个表，表中有人员的ID，姓名，年龄，我想写一个sql语句，得出小于20的有多少人？20--30，30--40，40-50，50-60，60-70，大于70的人数，sql语句怎么写呀？

[Neo_whl]

select 
         sum(case when age<20 then 1 end) as [<20],
         sum(case when age between 20 and 30 then 1 end) as [20-30],
         sum(case when age>30 and age<=40 then 1 end) as [30-40] ,
         sum(case when age>40 and age<=50 then 1 end) as [40-50] ,
         sum(case when age>50 and age<=60 then 1 end) as [50-60] ,
         sum(case when age>60 and age<=70 then 1 end) as [60-70],
         sum(case when age>70 then 1 end) as [>70]
         from tbTemp

[ytfhwfnh]

用count + case when语句

[江南小鱼]

with tbTemp(ID,name,age) as(
select '1001','Jimmy','20'
union all
select '1002','Tom','35'
union all
select '1003','John','85'
union all
select '1004','John1','75'
union all
select '1005','John2','25'
union all
select '1006','John3','65'
)
select case when age<20 then '小于20'
	when age between 20 and 30 then '20-30'
	when age between 31 and 40 then '30-40'
	when age between 41 and 50 then '40-50'
	when age between 51 and 60 then '50-60'
	when age between 61 and 70 then '60-70'
	when age>70 then '70以上' end Item
	,count(1) Numbers
from tbTemp
group by case when age<20 then '小于20'
	when age between 20 and 30 then '20-30'
	when age between 31 and 40 then '30-40'
	when age between 41 and 50 then '40-50'
	when age between 51 and 60 then '50-60'
	when age between 61 and 70 then '60-70'
	when age>70 then '70以上' end

[江南小鱼]

上面输入结果

Item	Numbers
20-30	2
30-40	1
60-70	1
70以上	2

也可以把结果集进行行转列输出

[20-30] [30-40] [60-70] [70以上]
2           2          1          2

[HelloWordGirl]

使用case when 语句