sqlserver按年龄段统计人数

已知：现有一张职工信息表，职工编号，职工姓名，职工生日，公司编码

求：统计根据输入的公司编码统计改公司各年龄段的人数 年龄段分别为 30岁以下，31-35，36-45，46-55，56以上；

 

初始化数据如下；

CREATE TABLE lzy_test(
 [id] [int] NOT NULL,
 [name] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 [birthday] [datetime] NULL,
 [school_code] [int] NULL,
 CONSTRAINT [PK_lzy_test] PRIMARY KEY CLUSTERED
)

INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (1,'name1',{ts '2010-01-01 00:00:00.'},1)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (2,'name2',{ts '1989-01-02 00:00:00.'},1)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (3,'name3',{ts '1978-02-07 00:00:00.'},1)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (4,'name4',{ts '1971-01-08 00:00:00.'},1)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (5,'name5',{ts '1990-09-09 00:00:00.'},1)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (9,'name9',{ts '1981-01-08 00:00:00.'},1)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (10,'name10',{ts '1992-08-07 00:00:00.'},2)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (11,'name11',{ts '1965-08-08 00:00:00.'},2)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (12,'name12',{ts '1960-08-08 00:00:00.'},2)
GO
INSERT INTO lzy_test (id,name,birthday,school_code) VALUES (13,'name13',{ts '1967-04-08 00:00:00.'},2)
GO

 

解答问题：
select school_code,(
case((case(sign(convert(varchar(100),getdate()-DATEADD(year,datediff(year,h.personal_birthday,getdate()),h.personal_birthday),112)-19000101))
         when 0 then datediff(year,h.personal_birthday,getdate())+1
         when 1 then datediff(year,h.personal_birthday,getdate())+1
         when -1 then datediff(year,h.personal_birthday,getdate())
         end)-1)/5+1) 
when 1 then '30岁以下'
when 2 then '30岁以下'
when 3 then '30岁以下'
when 4 then '30岁以下'
when 5 then '30岁以下'
when 6 then '30岁以下'  
when 7 then '31-35'
when 8 then'36-45'
when 9 then'36-45'
when 10 then'46-55'
when 11 then'46-55'
else '56以上'
end) as 年龄段,  
count(*) as 人数  
from lzy_test 
group by (
case(((case(sign(convert(varchar(100),getdate()-DATEADD(year,datediff(year,h.personal_birthday,getdate()),h.personal_birthday),112)-19000101))
         when 0 then datediff(year,h.personal_birthday,getdate())+1
         when 1 then datediff(year,h.personal_birthday,getdate())+1
         when -1 then datediff(year,h.personal_birthday,getdate())
         end)-1)/5+1)  
when 1 then '30岁以下'
when 2 then '30岁以下'
when 3 then '30岁以下'
when 4 then '30岁以下'
when 5 then '30岁以下'
when 6 then '30岁以下'  
when 7 then '31-35'
when 8 then'36-45'
when 9 then'36-45'
when 10 then'46-55'
when 11 then'46-55'
else '56以上'  
end   )
,school_code