问题是:根据用户的出生日期和当前日期，计算他最近的生日。

首先创建这样一个表（其中employees为MySQL官方提供的数据库，可以在相应网站上下载到）

表结构为：

然后向表中插入10条记录，并补充插入1条特殊生日的记录

insert into birthdate_problem select * from employees.employees limit 10;
insert into birthdate_problem select 22222,'1992-02-29','A','B','M','2000-01-01';

最后返回的结果应该为：

解决方案：

select name,birthday,IF(cur>next , cur , next) as birth_date
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+1 year) as cur,
DATE_ADD(birthday , INTERVAL diff 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 birthdate_problem
) as a
) as b
) as c;

用到了简单的date_add函数，而now是获取当前时间，year则是提取指定日期的年份。

ITPub博客

MySQL 计算生日问题