表employee(empid,birthday)
部分数据:
empid birthday
1 1964-3-2
2 1980-5-9
3 1972-9-30
....
想得到按年龄段的人数,比如说20-30岁的员工数,30-40岁的员工数,40-50岁的员工数,50岁以上的员工数
即由员工表得到以下的统计数据
AgeLevel count
20-30 30
30-40 5
40-50 8
50岁以上 7
怎样写这条语句?
语句如下::
select N'AgeLevel'=(case((datediff(year,birthday,getdate())-1)/10) when 2 then '21-30' when 3 then '31-40' when 4 then'41-50' else '50以上' end),
count(*) as count
from
employee
group by (case((datediff(year,birthday,getdate())-1)/10) when 2 then '21-30' when 3 then '31-40' when 4 then'41-50' else '50以上' end )
或者以10岁为递增
select
cast(f1*10+1 as varchar(3))+'-'+cast(f1*10+10 as varchar(3)) as 年龄段,f2 as 人数
from
(
select datediff(d,A0111,getdate())/365/10 as f1,
count(*) as f2
from dbo.A001
group by datediff(d,A0111,getdate())/365/10) a
order by cast(f1*10+1 as varchar(3))+'-'+cast(f1*10+10 as varchar(3))
第二种形式
16-20 21-30 31-40 41-50 51-60 61-70
0 8 9 8 5 3
SELECT
SUM(
CASE WHEN datediff(year, A0111, getdate()) BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS '16-20',
SUM(CASE WHEN datediff(year, A0111, getdate()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS '21-30',
SUM(CASE WHEN datediff(year, A0111, getdate()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '31-40',
SUM(CASE WHEN datediff(year, A0111, getdate()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '41-50',
SUM(CASE WHEN datediff(year, A0111, getdate()) BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS '51-60',
SUM(CASE WHEN datediff(year, A0111, getdate()) BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '61-70'
FROM
dbo.A001