关于日期的经典SQL编程问题：生日问题_MySQL

bitsCN.com

 

与日期相关的第一个问题是根据某个用户的出生日期和当前日期，计算他最近的生日。通过对这个问题的处理，演示如何通过使用日期函数来正确处理闰月。

　　在生日问题中，一般对闰月的处理如下：如果是闰月，那么返回2月28日;如果不是闰月，则返回3月1日(大部分是出于法律的要求)。例如，当前的日期是2005年9月26日，有人出生在1972年2月29日，查询后返回的该用户最近的生日应该是2006年3月1日。如果当前是2007年9月26日，那么查询后应该返回2008年2月29日。

　　在解决该问题之前，运行下列清单中的代码，初始化一些数据。在演示前，需要确认已经安装了MySQL官方的示例数据库employees。

USE test;

CREATE TABLE employees LIKE employees.employees;

INSERT INTO employees 
SELECT * FROM employees.employees LIMIT 10;

INSERT INTO employees 
SELECT 10011,'1972-02-29','Jiang','David','M','1990-2-20';

　　这里人为地插入一个员工David Jiang，其出生日期为“1972-02-29”，闰月。运行如下语句得到所有员工的出生信息。

SELECT 
    CONCAT(last_name,' ',first_name) AS Name,
    birth_date AS BirthDay 
FROM employees;

　　运行结果如下表所示。

▲每个员工的生日信息

　　下面是该解决方案的SQL查询：

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

　　这个查询需要a、b、c三个子查询来完成。第一个子查询a用来计算每位员工的出生日期与当前日期相差的年份，以及当前的日期。如果只运行子查询a，将得到如下表所示的输出，假设当前的日期为“2011-02-04”。

▲子查询a的结果

　　要计算某员工最近的生日，需要在BirthDay列加上Diff列的年数。如果结果大于当前日期，则年龄需要再加一年。子查询b增加两列即Cur和Next，这两列分别用于表示今年和明年的生日。注意，如果出生日期是2月29日，且目标日期不是闰月，那么这两列所包含的将是2月28日，而不是3月1日。子查询b的结果如下表所示。

▲子查询b的结果

　　子查询c用来处理闰月的问题，如果出生的日期为闰月，并且当前的年份不是闰年，则日期加1，表示3月1日为生日。对下一个年份使用同样的操作，子查询c的结果如下表所示。

▲子查询c的结果

　　最后判断今年的生日是否已过，如果是，则返回下一年的生日，最后得到的查询结果如下表所示。

▲最后得到的查询结果

　　可以看到Maliniak Kyoichi今年的生日已过，下一个生日是2012年，而David Jiang的生日是3月1日。

　　作者简介

　　姜承尧(DavidJiang)，《MySQL技术内幕：SQL编程》、《MySQL技术内幕：InnoDB存储引擎》作者，资深MySQL数据库专家，MySQL开源分支版本InnoSQL的创始人，独立数据库咨询顾问。不仅擅长于数据库的管理和维护，还擅长于数据库的开发，同时一直致力于MySQL数据库底层实现原理的研究和探索，对高性能数据库和数据仓库也有深刻而独到的理解。目前就职于网易研究院，担任后台技术中心技术经理一职，从事MySQL数据库底层以及云的相关的开发工作。

bitsCN.com