当前位置:Gxlcms > mysql > 关于日期的经典SQL编程问题:生日问题_MySQL

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

时间:2021-07-01 10:21:17 帮助过:46人阅读

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编程问题:生日问题
▲每个员工的生日信息

  下面是该解决方案的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”。

关于日期的经典SQL编程问题:生日问题
▲子查询a的结果

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

关于日期的经典SQL编程问题:生日问题
▲子查询b的结果

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

关于日期的经典SQL编程问题:生日问题
▲子查询c的结果

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

关于日期的经典SQL编程问题:生日问题
▲最后得到的查询结果

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

  作者简介

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

bitsCN.com

人气教程排行