生日问题的题目要求是求每个人最近的生日。可以算是比较经典的和日期相关的SQL问题了。

思考时主要是要注意两点：一是关于闰月的处理，一般2月29日遇到非闰月要处理成3月1日；二是通过每一层子循环得到哪些信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT name, birthday, IF(cur > today, cur, next) AS birth_day
FROM (
	SELECT name, birthday, today,
		DATE_ADD(cur, INTERVAL IF(DAY(birthday)=29 && DAY(cur)=28, 1, 0) DAY) AS cur,
		DATE_ADD(next, INTERVAL IF(DAY(birthday)=29 && DAY(next)=28, 1, 0) DAY) AS next
	FROM (
		SELECT name, birthday, today,
			DATE_ADD(birthday, INTERVAL diff YEAR) AS cur,
			DATE_ADD(birthday, INTERVAL diff+1 YEAR) AS next
		FROM (
			SELECT CONCAT(last_name, ' ', first_name) AS name,
				birth_date AS birthday,
				(YEAR(NOW()) - YEAR(birth_date)) AS diff,
				NOW() AS today
			FROM employees
		) AS a
	) AS b
) AS c