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)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (2,'name2',{ts '1989-01-02 00:00:00.'},1)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (3,'name3',{ts '1978-02-07 00:00:00.'},1)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (4,'name4',{ts '1971-01-08 00:00:00.'},1)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (5,'name5',{ts '1990-09-09 00:00:00.'},1)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (9,'name9',{ts '1981-01-08 00:00:00.'},1)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (10,'name10',{ts '1992-08-07 00:00:00.'},2)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (11,'name11',{ts '1965-08-08 00:00:00.'},2)GOINSERT INTO lzy_test (id,name,birthday,school_code) VALUES (12,'name12',{ts '1960-08-08 00:00:00.'},2)GOINSERT 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

原文章浏览量：4377

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-740995