MySQL 根据用户出生日期计算最近生日


表结构与测试数据

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employees` */

insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
insert  into `employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) values (10011,'1972-02-29','Jiang','David','M','1990-02-20');

说明

在生日问题中,一般对闰月的处理如下:如果是闰月,那么返回 2 月 28 日;如果不是闰月,则返回 3 月 1 日
例如,当前的日期是 2005 年 9 月 26 日,有人出生在 1972 年 2 月 29 日, 查询后返回的该用户最近的生日应该是 2006 年 3 月 1日,如果当前日期是 2007 年 9 月 26 日,那么查询后应该返回 2008 年 2 月 29 日

过程

1、查询出生日期与当前日期的相差年份与当前日期

mysql> SELECT
    ->     CONCAT(last_name, ' ', first_name) AS pname,
    ->     birth_date AS birthday,
    ->     ( YEAR( NOW() ) )-YEAR(birth_date) AS diff,
    ->     NOW() AS today
    -> FROM employees;
+--------------------+------------+------+---------------------+
| pname              | birthday   | diff | today               |
+--------------------+------------+------+---------------------+
| Facello Georgi     | 1953-09-02 |   63 | 2016-01-17 11:46:23 |
| Simmel Bezalel     | 1964-06-02 |   52 | 2016-01-17 11:46:23 |
| Bamford Parto      | 1959-12-03 |   57 | 2016-01-17 11:46:23 |
| Koblick Chirstian  | 1954-05-01 |   62 | 2016-01-17 11:46:23 |
| Maliniak Kyoichi   | 1955-01-21 |   61 | 2016-01-17 11:46:23 |
| Preusig Anneke     | 1953-04-20 |   63 | 2016-01-17 11:46:23 |
| Zielinski Tzvetan  | 1957-05-23 |   59 | 2016-01-17 11:46:23 |
| Kalloufi Saniya    | 1958-02-19 |   58 | 2016-01-17 11:46:23 |
| Peac Sumant        | 1952-04-19 |   64 | 2016-01-17 11:46:23 |
| Piveteau Duangkaew | 1963-06-01 |   53 | 2016-01-17 11:46:23 |
| David Jiang        | 1972-02-29 |   44 | 2016-01-17 11:46:23 |
+--------------------+------------+------+---------------------+
11 rows in set (0.00 sec)

2、计算最近生日需要 birthday 列加上 diff 列的年数,如果结果大于当前日期,则年龄需要加一年,所以需要查询出今年与明年的生日列

mysql> SELECT
    ->     pname, birthday, today,
    ->     DATE_ADD(birthday, INTERVAL diff YEAR) AS cur_birthday,
    ->     DATE_ADD(birthday, INTERVAL diff+1 YEAR) AS next_birthday
    -> FROM(
    ->
    ->     SELECT
    ->         CONCAT(last_name, ' ', first_name) AS pname,
    ->         birth_date AS birthday,
    ->         ( YEAR( NOW() ) )-YEAR(birth_date) AS diff,
    ->         NOW() AS today
    ->     FROM employees
    ->
    -> ) AS a;
+--------------------+------------+---------------------+--------------+---------------+
| pname              | birthday   | today               | cur_birthday | next_birthday |
+--------------------+------------+---------------------+--------------+---------------+
| Facello Georgi     | 1953-09-02 | 2016-01-17 11:47:42 | 2016-09-02   | 2017-09-02    |
| Simmel Bezalel     | 1964-06-02 | 2016-01-17 11:47:42 | 2016-06-02   | 2017-06-02    |
| Bamford Parto      | 1959-12-03 | 2016-01-17 11:47:42 | 2016-12-03   | 2017-12-03    |
| Koblick Chirstian  | 1954-05-01 | 2016-01-17 11:47:42 | 2016-05-01   | 2017-05-01    |
| Maliniak Kyoichi   | 1955-01-21 | 2016-01-17 11:47:42 | 2016-01-21   | 2017-01-21    |
| Preusig Anneke     | 1953-04-20 | 2016-01-17 11:47:42 | 2016-04-20   | 2017-04-20    |
| Zielinski Tzvetan  | 1957-05-23 | 2016-01-17 11:47:42 | 2016-05-23   | 2017-05-23    |
| Kalloufi Saniya    | 1958-02-19 | 2016-01-17 11:47:42 | 2016-02-19   | 2017-02-19    |
| Peac Sumant        | 1952-04-19 | 2016-01-17 11:47:42 | 2016-04-19   | 2017-04-19    |
| Piveteau Duangkaew | 1963-06-01 | 2016-01-17 11:47:42 | 2016-06-01   | 2017-06-01    |
| David Jiang        | 1972-02-29 | 2016-01-17 11:47:42 | 2016-02-29   | 2017-02-28    |
+--------------------+------------+---------------------+--------------+---------------+
11 rows in set (0.01 sec)

3、如果出生日期是 2 月 29 日,且目标日期不是闰年,则 cur_birthday 与 next_birthday 两列 将是 2 月 28 日, 否则为 3 月 1 日

mysql> SELECT
    ->     pname, birthday, today,
    ->     DATE_ADD(cur_birthday, INTERVAL IF(DAY(birthday)=29 && DAY(cur_birthday)=28, 1, 0) DAY) AS cur_birthday,
    ->     DATE_ADD(next_birthday, INTERVAL IF(DAY(birthday)=29 && DAY(next_birthday)=28, 1, 0) DAY) AS next_birthday
    -> FROM(
    ->
    ->     SELECT
    ->         pname, birthday, today,
    ->         DATE_ADD(birthday, INTERVAL diff YEAR) AS cur_birthday,
    ->         DATE_ADD(birthday, INTERVAL diff+1 YEAR) AS next_birthday
    ->     FROM(
    ->
    ->         SELECT
    ->             CONCAT(last_name, ' ', first_name) AS pname,
    ->             birth_date AS birthday,
    ->             ( YEAR( NOW() ) )-YEAR(birth_date) AS diff,
    ->             NOW() AS today
    ->         FROM employees
    ->
    ->     ) AS a
    ->
    -> ) AS b;
+--------------------+------------+---------------------+--------------+---------------+
| pname              | birthday   | today               | cur_birthday | next_birthday |
+--------------------+------------+---------------------+--------------+---------------+
| Facello Georgi     | 1953-09-02 | 2016-01-17 11:52:09 | 2016-09-02   | 2017-09-02    |
| Simmel Bezalel     | 1964-06-02 | 2016-01-17 11:52:09 | 2016-06-02   | 2017-06-02    |
| Bamford Parto      | 1959-12-03 | 2016-01-17 11:52:09 | 2016-12-03   | 2017-12-03    |
| Koblick Chirstian  | 1954-05-01 | 2016-01-17 11:52:09 | 2016-05-01   | 2017-05-01    |
| Maliniak Kyoichi   | 1955-01-21 | 2016-01-17 11:52:09 | 2016-01-21   | 2017-01-21    |
| Preusig Anneke     | 1953-04-20 | 2016-01-17 11:52:09 | 2016-04-20   | 2017-04-20    |
| Zielinski Tzvetan  | 1957-05-23 | 2016-01-17 11:52:09 | 2016-05-23   | 2017-05-23    |
| Kalloufi Saniya    | 1958-02-19 | 2016-01-17 11:52:09 | 2016-02-19   | 2017-02-19    |
| Peac Sumant        | 1952-04-19 | 2016-01-17 11:52:09 | 2016-04-19   | 2017-04-19    |
| Piveteau Duangkaew | 1963-06-01 | 2016-01-17 11:52:09 | 2016-06-01   | 2017-06-01    |
| David Jiang        | 1972-02-29 | 2016-01-17 11:52:09 | 2016-02-29   | 2017-03-01    |
+--------------------+------------+---------------------+--------------+---------------+
11 rows in set (0.00 sec)

4、最后判断当年生日是否已过,是则返回下一年生日

mysql> SELECT
    ->     pname, birthday, IF(cur_birthday>today, cur_birthday, next_birthday) AS recent_birthday
    -> FROM(
    ->
    ->     SELECT
    ->         pname, birthday, today,
    ->         DATE_ADD(cur_birthday, INTERVAL IF(DAY(birthday)=29 && DAY(cur_birthday)=28, 1, 0) DAY) AS cur_birthday,
    ->         DATE_ADD(next_birthday, INTERVAL IF(DAY(birthday)=29 && DAY(next_birthday)=28, 1, 0) DAY) AS next_birthday
    ->     FROM(
    ->
    ->         SELECT
    ->             pname, birthday, today,
    ->             DATE_ADD(birthday, INTERVAL diff YEAR) AS cur_birthday,
    ->             DATE_ADD(birthday, INTERVAL diff+1 YEAR) AS next_birthday
    ->         FROM(
    ->
    ->             SELECT
    ->                 CONCAT(last_name, ' ', first_name) AS pname,
    ->                 birth_date AS birthday,
    ->                 ( YEAR( NOW() ) )-YEAR(birth_date) AS diff,
    ->                 NOW() AS today
    ->             FROM employees
    ->
    ->         ) AS a
    ->
    ->     ) AS b
    ->
    -> ) AS c;
+--------------------+------------+-----------------+
| pname              | birthday   | recent_birthday |
+--------------------+------------+-----------------+
| Facello Georgi     | 1953-09-02 | 2016-09-02      |
| Simmel Bezalel     | 1964-06-02 | 2016-06-02      |
| Bamford Parto      | 1959-12-03 | 2016-12-03      |
| Koblick Chirstian  | 1954-05-01 | 2016-05-01      |
| Maliniak Kyoichi   | 1955-01-21 | 2016-01-21      |
| Preusig Anneke     | 1953-04-20 | 2016-04-20      |
| Zielinski Tzvetan  | 1957-05-23 | 2016-05-23      |
| Kalloufi Saniya    | 1958-02-19 | 2016-02-19      |
| Peac Sumant        | 1952-04-19 | 2016-04-19      |
| Piveteau Duangkaew | 1963-06-01 | 2016-06-01      |
| David Jiang        | 1972-02-29 | 2016-02-29      |
+--------------------+------------+-----------------+
11 rows in set (0.00 sec)

最终SQL:

SELECT 
    pname, birthday, IF(cur_birthday>today, cur_birthday, next_birthday) AS recent_birthday 
FROM(

    SELECT 
        pname, birthday, today, 
        DATE_ADD(cur_birthday, INTERVAL IF(DAY(birthday)=29 && DAY(cur_birthday)=28, 1, 0) DAY) AS cur_birthday, 
        DATE_ADD(next_birthday, INTERVAL IF(DAY(birthday)=29 && DAY(next_birthday)=28, 1, 0) DAY) AS next_birthday
    FROM(
    
        SELECT 
            pname, birthday, today, 
            DATE_ADD(birthday, INTERVAL diff YEAR) AS cur_birthday, 
            DATE_ADD(birthday, INTERVAL diff+1 YEAR) AS next_birthday
        FROM(
        
            SELECT 
                CONCAT(last_name, ' ', first_name) AS pname, 
                birth_date AS birthday, 
                ( YEAR( NOW() ) )-YEAR(birth_date) AS diff, 
                NOW() AS today
            FROM employees
            
        ) AS a
        
    ) AS b

) AS c

标签: mysql, 最近生日, 近期生日, 出生日期