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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29773961/viewspace-1248564/