關於日期的經典SQL編程問題:生日問題

　　【IT168 技術】與日期相關的第一個問題是根據某個用戶的出生日期和當前日期，計算他最近的生日。通過對這個問題的處理，演示如何通過使用日期函數來正確處理閏月。

　　在生日問題中，一般對閏月的處理如下：如果是閏月，那麼返回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資料庫底層以及雲的相關的開發工作。