【MySQL】一条SQL查询本年度每月即将退休的人数？

通过SQL来查询本年度员工退休情况统计，其中男性60周岁退休，女性副处级以上（含副处）60周岁退休，副处级以下则是55周岁退休。

0、本年度第一天

$year_start = date('Y-01-01 00:00:00');

1、先得出用户的周岁年龄

SELECT TIMESTAMPDIFF(YEAR, @birthday, '$year_start') ;

2、再通过条件进行判断

SELECT SUM(mn.tag) sum,mn.mon FROM (
	SELECT a.sex,TIMESTAMPDIFF(YEAR, a.birthday, '$year_start') AS age,b.职级,DATE_FORMAT(a.birthday,'%m') month,
	CASE WHEN a.sex = 2 AND b.职级 < '副处' AND TIMESTAMPDIFF(YEAR, a.birthday, '$year_start') > 55 THEN 0
	WHEN a.sex = 2 AND b.职级 >=  '副处' THEN 1
	WHEN a.sex = 1 AND TIMESTAMPDIFF(YEAR, a.birthday, '$year_start') <= 59 THEN 0
	ELSE 1 END tag
	FROM ALL_USER_TABLE a
	LEFT JOIN (
			SELECT u.* FROM (
				SELECT PersonID,职级 FROM `职级从表` 
				ORDER BY DispOrder DESC 
			) u GROUP BY u.PersonID
	) b ON a.PersonID = b.PersonID 
	WHERE TIMESTAMPDIFF(YEAR, a.birthday, '$year_start') > 54 AND TIMESTAMPDIFF(YEAR, a.birthday, '$year_start') <= 60
) mn GROUP BY mn.mon;

3、得到结果如下：