select [name],'a1(ages<20)'=sum(case when ages<20 then 1 else 0 end),
'a2(ages<30)'=sum(case when ages between 20 and 29 then 1 else 0 end),
'a3(ages<40)'=sum(case when ages between 30 and 39 then 1 else 0 end),
'a4(ages<50)'=sum(case when ages between 40 and 49 then 1 else 0 end),
'a5(ages<60)'=sum(case when ages between 50 and 59 then 1 else 0 end)
from pepole group by [name]
'a2(ages<30)'=sum(case when ages between 20 and 29 then 1 else 0 end),
'a3(ages<40)'=sum(case when ages between 30 and 39 then 1 else 0 end),
'a4(ages<50)'=sum(case when ages between 40 and 49 then 1 else 0 end),
'a5(ages<60)'=sum(case when ages between 50 and 59 then 1 else 0 end)
from pepole group by [name]
insert into pepole select '黄司',23
insert into pepole select '黄司',34
insert into pepole select '黄司',21
insert into pepole select '小李',15
insert into pepole select '小李',34
insert into pepole select '小李',22
insert into pepole select '小李',23
insert into pepole select '小李',12
insert into pepole select '刘辉',22
insert into pepole select '刘辉',24
insert into pepole select '刘辉',11
insert into pepole select '刘辉',50select
name ,
[a1(ages<20)] = sum(case when ages < 20 then 1 else 0 end),
[a2(ages<30)] = sum(case when ages>=20 and ages<30 then 1 else 0 end),
[a3(ages<40)] = sum(case when ages>=30 and ages<40 then 1 else 0 end),
[a4(ages<50)] = sum(case when ages>=40 and ages<50 then 1 else 0 end),
[a5(ages<60)] = sum(case when ages>=50 and ages<60 then 1 else 0 end)
from
pepole
group by
namedrop table pepole
sum(case when ages<20 then 1 else 0 end) as [a1(ages<20)],
sum(case when ages between 20 and 29 then 1 else 0 end) as [a2(2ages<30)],
sum(case when ages between 30 and 39 then 1 else 0 end) as [a3(ages<40)],
sum(case when ages between 40 and 49 then 1 else 0 end) as [a4(ages<50)],
sum(case when ages between 50 and 59 then 1 else 0 end) as [a5(ages<60)]
from pepole group by [name]
create table #pepole(name char(8),ages int)
insert #pepole values('黄司',23)
insert #pepole values('黄司',34)
insert #pepole values('黄司',21)
insert #pepole values('小李',15)
insert #pepole values('小李',34)
insert #pepole values('小李',22)
insert #pepole values('小李',23)
insert #pepole values('小李',12)
insert #pepole values('刘辉',22)
insert #pepole values('刘辉',24)
insert #pepole values('刘辉',11)
insert #pepole values('刘辉',50)
go--查询
select name,isnull(sum(case when ages<20 then 1 end),0) as 'ages<20',
isnull(sum(case when ages<30 and ages>20 then 1 end),0) as 'ages<30',
isnull(sum(case when ages<40 and ages>30 then 1 end),0) as 'ages<40',
isnull(sum(case when ages<50 and ages>40 then 1 end),0) as 'ages<50',
isnull(sum(case when ages<60 and ages>50 then 1 end),0) as 'ages<60'
from #pepole group by name--删除测试表
drop table #pepole
sum(case when ages<20 then 1 else 0 end) as 'a1(ages<20)',
sum(case when 20<=ages and ages<30 then 1 else 0 end) as 'a1(ages<30)',
sum(case when 30<=ages and ages<40 then 1 else 0 end) as 'a1(ages<40)',
sum(case when 40<=ages and ages<50 then 1 else 0 end) as 'a1(ages<50)',
sum(case when 50<=ages and ages<60 then 1 else 0 end) as 'a1(ages<60)'
from pepole
group by name
sum(case when ages<20 then 1 else 0 end) as [a1(ages<20)],
sum(case when ages between 20 and 29 then 1 else 0 end) as [a2(2ages<30)],
sum(case when ages between 30 and 39 then 1 else 0 end) as [a3(ages<40)],
sum(case when ages between 40 and 49 then 1 else 0 end) as [a4(ages<50)],
sum(case when ages between 50 and 59 then 1 else 0 end) as [a5(ages<60)]
from pepole group by [name]