怎么根据生日把人员数据按年限分类_Sql Server

如何根据生日把人员数据按年限分类?
有一堆人(含生日)想统计下类似这样情况
35岁以下
36-45岁
46-55岁
56-65岁
65岁以上

------解决方案--------------------

SQL code

select type , count(*) from (  select * , case when datediff(year,birthday,getdate()) <= 35 then '35岁以下'                 when datediff(year,birthday,getdate()) >= 36 and datediff(year,birthday,getdate()) <= 45 then '36-45岁'                when datediff(year,birthday,getdate()) >= 46 and datediff(year,birthday,getdate()) <= 55 then '46-55岁'                when datediff(year,birthday,getdate()) >= 56 and datediff(year,birthday,getdate()) <= 65 then '56-65岁'                when datediff(year,birthday,getdate()) > 65 and  then '65岁以上'           end type  from tb) tgroup by type
------解决方案--------------------
SQL codeselect type , count(*) from (  select * , case when datediff(year,birthday,getdate()) <= 35 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '35岁以下'                  when datediff(year,birthday,getdate()) between 36 and 45 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '36-45岁'                 when datediff(year,birthday,getdate()) between 46 and 55 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '46-55岁'                 when datediff(year,birthday,getdate()) between 56 and 65 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '56-65岁'                 when datediff(year,birthday,getdate()) > 65 and datepart(month,birthday>datepart(month,getdate()) and datepart(day,birthday)>datepart(day,getdate()) then '65岁以上'            end type  from tb) tgroup by type