MySQL 計算生日問題

神諭丶發表於2014-08-10
MySql
問題是:根據使用者的出生日期和當前日期，計算他最近的生日。



首先建立這樣一個表（其中employees為MySQL官方提供的資料庫，可以在相應網站上下載到）
  1. create table birthdate_problem like employees.employees;

表結構為：



然後向表中插入10條記錄，並補充插入1條特殊生日的記錄


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


最後返回的結果應該為：



解決方案：


  1. select name,birthday,IF(cur>next , cur , next) as birth_date
  2. from (    select name,birthday,today,
  3.             DATE_ADD(cur , INTERVAL IF(DAY(birthday)=29 && DAY(cur)=28,1,0) DAY) as cur,
  4.             DATE_ADD(next , INTERVAL IF(DAY(birthday)=29 && DAY(next)=28,1,0) DAY) as next
  5.         from (    select name,birthday,today,
  6.                     DATE_ADD(birthday , INTERVAL diff+1 year) as cur,
  7.                     DATE_ADD(birthday , INTERVAL diff year) as next
  8.                 from (    select concat(last_name,' ',first_name) as name,
  9.                                 birth_date as birthday,
  10.                                 (year(now())-year(birth_date)) as diff,
  11.                                 now() as today
  12.                         from birthdate_problem
  13.                     ) as a
  14.         )    as b
  15. ) as c;

用到了簡單的date_add函式，而now是獲取當前時間，year則是提取指定日期的年份。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1248564/，如需轉載，請註明出處，否則將追究法律責任。

相關文章