sql 年龄段统计

表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