关于年龄段的统计问题!

[loulanlouzhu]

人员信息表:

EmployeeID(工号) BornDate(出生日期) WorkModality(用工形式) Property(工作性质)


注:用工形式分若干中,假设为A.B.C.D

每种用工形式都有四种工作性质(W,X,Y,Z)

想得到每种用工形式的各个工作性质在各个年龄段的人数,平均年龄

年龄段分18~20,21~25,26~30,31~35,36~40,41~45,46~50,51~55,56以上,

[wzh1215]

18-20：
select workmodality,property,count(*) as 人数,avg(datadiff(yy,borndate,getdate())) as 平均年龄 from 表 where datediff(yy,borndate,getdate())>=18 and datediff(yy,borndate,getdate())<=20 group by workmodality,property

[pengdali]

select WorkModality,Property,

case
when datediff(year,BornDate,getdate()) between 18 and 20 then '18~20'
when datediff(year,BornDate,getdate()) between 21 and 25 then '21~25'
when datediff(year,BornDate,getdate()) between 26 and 30 then '26~30'
when datediff(year,BornDate,getdate()) between 31 and 35 then '31~35'
when datediff(year,BornDate,getdate()) between 36 and 40 then '36~40'
when datediff(year,BornDate,getdate()) between 41 and 45 then '41~45'
when datediff(year,BornDate,getdate()) between 46 and 50 then '46~50'
when datediff(year,BornDate,getdate()) between 51 and 55 then '51~55'
when datediff(year,BornDate,getdate())>=56 then '56以上'
end 年龄段,

count(*) 人数,
avg(datediff(year,BornDate,getdate())) 平均年龄

from 人员信息表 group by WorkModality,Property,
case
when datediff(year,BornDate,getdate()) between 18 and 20 then '18~20'
when datediff(year,BornDate,getdate()) between 21 and 25 then '21~25'
when datediff(year,BornDate,getdate()) between 26 and 30 then '26~30'
when datediff(year,BornDate,getdate()) between 31 and 35 then '31~35'
when datediff(year,BornDate,getdate()) between 36 and 40 then '36~40'
when datediff(year,BornDate,getdate()) between 41 and 45 then '41~45'
when datediff(year,BornDate,getdate()) between 46 and 50 then '46~50'
when datediff(year,BornDate,getdate()) between 51 and 55 then '51~55'
when datediff(year,BornDate,getdate())>=56 then '56以上'
end 年龄段

[pengdali]

select WorkModality,Property,

case
when datediff(year,BornDate,getdate()) between 18 and 20 then '18~20'
when datediff(year,BornDate,getdate()) between 21 and 25 then '21~25'
when datediff(year,BornDate,getdate()) between 26 and 30 then '26~30'
when datediff(year,BornDate,getdate()) between 31 and 35 then '31~35'
when datediff(year,BornDate,getdate()) between 36 and 40 then '36~40'
when datediff(year,BornDate,getdate()) between 41 and 45 then '41~45'
when datediff(year,BornDate,getdate()) between 46 and 50 then '46~50'
when datediff(year,BornDate,getdate()) between 51 and 55 then '51~55'
when datediff(year,BornDate,getdate())>=56 then '56以上'
end 年龄段,

count(*) 人数,
avg(datediff(year,BornDate,getdate())) 平均年龄

from 人员信息表 group by WorkModality,Property,
case
when datediff(year,BornDate,getdate()) between 18 and 20 then '18~20'
when datediff(year,BornDate,getdate()) between 21 and 25 then '21~25'
when datediff(year,BornDate,getdate()) between 26 and 30 then '26~30'
when datediff(year,BornDate,getdate()) between 31 and 35 then '31~35'
when datediff(year,BornDate,getdate()) between 36 and 40 then '36~40'
when datediff(year,BornDate,getdate()) between 41 and 45 then '41~45'
when datediff(year,BornDate,getdate()) between 46 and 50 then '46~50'
when datediff(year,BornDate,getdate()) between 51 and 55 then '51~55'
when datediff(year,BornDate,getdate())>=56 then '56以上'
end

[realgz]

group by case ()

[loulanlouzhu]

18~20 21~25 26~30 31~35 36~40 41~45 46~50 51~55 56以上

我想把每个年龄段的人数作为列来显示!!可以在sql 中完成吗@?


===弯弯的月亮小小的船，小小的船，两头尖，我在小小的船里坐，只看见闪闪
的星星蓝蓝的天．

===本贴子以“现状”提供且没有任何担保，同时也没有授予任何权利

[realgz]

那自然可以 select sum(case())就ok

[victorycyz]

问一下高手们，这里的case语句有没有办法写成类似下面这种形式：
case
datediff(year,BornDate,getdate())
when between 18 and 20 then '18~20'
when between 21 and 25 then '21~25'
...
end 年龄段

---------------------------------------------------
原case语句:
case
when datediff(year,BornDate,getdate()) between 18 and 20 then '18~20'
when datediff(year,BornDate,getdate()) between 21 and 25 then '21~25'
when datediff(year,BornDate,getdate()) between 26 and 30 then '26~30'
when datediff(year,BornDate,getdate()) between 31 and 35 then '31~35'
when datediff(year,BornDate,getdate()) between 36 and 40 then '36~40'
when datediff(year,BornDate,getdate()) between 41 and 45 then '41~45'
when datediff(year,BornDate,getdate()) between 46 and 50 then '46~50'
when datediff(year,BornDate,getdate()) between 51 and 55 then '51~55'
when datediff(year,BornDate,getdate())>=56 then '56以上'
end 年龄段,

[loulanlouzhu]

To:victorycyz(中海，干活去，别在CSDN玩耍！)

我试了下,好像不可以!


===弯弯的月亮小小的船，小小的船，两头尖，我在小小的船里坐，只看见闪闪
的星星蓝蓝的天．

===本贴子以“现状”提供且没有任何担保，同时也没有授予任何权利

[zjcxc]

select Property
,[18~20]=sum(case when old between 18 and 20 then 1 else 0 end)
,[21~25]=sum(case when old between 21 and 25 then 1 else 0 end)
,[26~30]=sum(case when old between 26 and 30 then 1 else 0 end)
,[31~35]=sum(case when old between 31 and 35 then 1 else 0 end)
,[36~40]=sum(case when old between 36 and 40 then 1 else 0 end)
,[41~45]=sum(case when old between 41 and 45 then 1 else 0 end)
,[46~50]=sum(case when old between 46 and 50 then 1 else 0 end)
,[51~55]=sum(case when old between 51 and 55 then 1 else 0 end)
,[56以上]=sum(case when old>55 then 1 else 0 end)
from(
select Property,old=datediff(year,BornDate,getdate())
from 人员信息表
) a
group by Property