問題是:根據使用者的出生日期和當前日期，計算他最近的生日。

首先建立這樣一個表（其中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則是提取指定日期的年份。

MySQL 計算生日問題

相關文章