今天在写一个人事系统时,碰到过这样的问题,按出生日期分类统计每个年代的员工人数,经过一番思考,今天总算把语句写出来了,特发布出来,供大家参考,如果有更好的方法,都可以提出来。
有一个人员信息表HREmployee,其中有两个字段:strWorkNo为员工编号,dtBirthDay为出生日期,现要按出生日期分类统计出每个年代的员工人数,即按(1940-1-1~1949-12-31,1950-1-1~1959-12-31,……)来分类统计,我写出的语句如下:
SELECT
COUNT
(strWorkNo) CountPer, YearFirst
FROM
(
SELECT strWorkNo, ' YearFirst ' = CASE WHEN dtBirthday Between CAST (( YEAR (dtBirthday) / 10 ) as varchar ( 4 )) + ' 0-1-1 '
and CAST (( YEAR (dtBirthday) / 10 ) as varchar ( 4 )) + ' 9-12-31 ' THEN CAST (( YEAR (dtBirthday) / 10 ) as varchar ( 4 )) + ' 0 '
ELSE ' 0 ' END
from HREmployee
) a
GROUP BY YearFirst
SELECT strWorkNo, ' YearFirst ' = CASE WHEN dtBirthday Between CAST (( YEAR (dtBirthday) / 10 ) as varchar ( 4 )) + ' 0-1-1 '
and CAST (( YEAR (dtBirthday) / 10 ) as varchar ( 4 )) + ' 9-12-31 ' THEN CAST (( YEAR (dtBirthday) / 10 ) as varchar ( 4 )) + ' 0 '
ELSE ' 0 ' END
from HREmployee
) a
GROUP BY YearFirst