第2问SQL语句!- SELECT 部门, 粗略计算年龄段,sum(人数) as 按部门分段 FROM
- (
- SELECT 部门,nld AS 粗略计算年龄段,count(*) as 人数 from
- (
- SELECT 部门, 姓名,datediff(yy,出生年月,getdate()) as 粗略年龄,
- case
- when datediff(yy,出生年月,GETDATE())>=36 and datediff(yy,出生年月,GETDATE()) <=40 THEN '36-40'
- when datediff(yy,出生年月,GETDATE())>=41 and datediff(yy,出生年月,GETDATE()) <=45 THEN '41-45'
- when datediff(yy,出生年月,GETDATE())>=46 and datediff(yy,出生年月,GETDATE()) <=50 THEN '46-50'
- when datediff(yy,出生年月,GETDATE())>=51 and datediff(yy,出生年月,GETDATE()) <=55 THEN '51-55'
- when datediff(yy,出生年月,GETDATE())>=56 and datediff(yy,出生年月,GETDATE()) <=60 THEN '56-60'
- when datediff(yy,出生年月,GETDATE())>=61 and datediff(yy,出生年月,GETDATE()) <=65 THEN '61-65'
- when datediff(yy,出生年月,GETDATE())>=66 and datediff(yy,出生年月,GETDATE()) <=70 THEN '66-70'
- when datediff(yy,出生年月,GETDATE())>=71 and datediff(yy,出生年月,GETDATE()) <=75 THEN '71-75'
- when datediff(yy,出生年月,GETDATE())>=76 and datediff(yy,出生年月,GETDATE()) <=80 THEN '76-80'
- when datediff(yy,出生年月,GETDATE())>=81 and datediff(yy,出生年月,GETDATE()) <=85 THEN '81-85'
- when datediff(yy,出生年月,GETDATE())>=86 and datediff(yy,出生年月,GETDATE()) <=90 THEN '86-90'
- END AS nld
- from dbo.Sheet1$
- ) a
- GROUP BY 部门,nld
- ) b
- GROUP BY 部门,粗略计算年龄段
- ORDER BY 部门
复制代码 |