频道栏目
读书频道 > 数据库 > Mysql > MySQL技术内幕:SQL编程
2.4.1 生日问题
2012-11-06 09:21:18     我来说两句
收藏   我要投稿

本文所属图书 > MySQL技术内幕:SQL编程

全书一共10章,全面探讨了MySQL中SQL编程的各种方法、技巧与最佳实践。第1章首先介绍了SQL编程的概念、数据库的应用类型以及SQL查询分析器,然后介绍了SQL编程的三个阶段,希望读者通过本书的学习能达到最后的融...  立即去当当网订购

2.4 关于日期的经典SQL编程问题

前面已经介绍了时间和日期类型及相关的函数,这一节将探讨与日期相关的SQL编程问题。

2.4.1 生日问题

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

在生日问题中,一般对闰月的处理如下:如果是闰月,那么返回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;

运行结果如表2-2所示。

表 2-2 每个员工的生日信息
Name BirthDay
Facello Georgi 1953-09-02
Simmel Bezalel 1964-06-02
Bamford Parto 1959-12-03
Koblick Chirstian 1954-05-01
Maliniak Kyoichi 1955-01-21
Preusig Anneke 1953-04-20
Zielinski Tzvetan 1957-05-23
Kalloufi Saniya 1958-02-19
Peac Sumant 1952-04-19
Piveteau Duangkaew 1963-06-01
David Jiang 1972-02-29

下面是该解决方案的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,将得到如表2-3所示的输出,假设当前的日期为“2011-02-04”。

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

表 2-3 子查询a的结果
Name BirthDay Diff Today
Facello Georgi 1953-09-02 58 2011-02-04
Simmel Bezalel 1964-06-02 47 2011-02-04
Bamford Parto 1959-12-03 52 2011-02-04
Koblick Chirstian 1954-05-01 57 2011-02-04
Maliniak Kyoichi 1955-01-21 56 2011-02-04
Preusig Anneke 1953-04-20 58 2011-02-04
Zielinski Tzvetan 1957-05-23 54 2011-02-04
Kalloufi Saniya 1958-02-19 53 2011-02-04
Peac Sumant 1952-04-19 59 2011-02-04
Piveteau Duangkaew 1963-06-01 48 2011-02-04
David Jiang   1972-02-29 39 2011-02-04

表 2-4 子查询b的结果
Name BirthDay Today Cur Next
Facello Georgi 1953-09-02 2011-02-04 2011-09-02 2012-09-02
Simmel Bezalel 1964-06-02 2011-02-04 2011-06-02 2012-06-02
Bamford Parto 1959-12-03 2011-02-04 2011-12-03 2012-12-03
Koblick Chirstian 1954-05-01 2011-02-04 2011-05-01 2012-05-01
Maliniak Kyoichi 1955-01-21 2011-02-04 2011-01-21 2012-01-21
Preusig Anneke 1953-04-20 2011-02-04 2011-04-20 2012-04-20
Zielinski Tzvetan 1957-05-23 2011-02-04 2011-05-23 2012-05-23
Kalloufi Saniya 1958-02-19 2011-02-04 2011-02-19 2012-02-19
Peac Sumant 1952-04-19 2011-02-04 2011-04-19 2012-04-19
Piveu Duangkaew 1963-06-01 2011-02-04 2011-06-01 2012-06-01
David Jiang 1972-02-29 2011-02-04 2011-02-28 2012-02-29

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

表 2-5 子查询c的结果
Name BirthDay Today Cur Next
Facello Georgi 1953-09-02 2011-02-04 2011-09-02 2012-09-02
Simmel Bezalel 1964-06-02 2011-02-04 2011-06-02 2012-06-02
Bamford Parto 1959-12-03 2011-02-04 2011-12-03 2012-12-03
Koblick Chirstian 1954-05-01 2011-02-04 2011-05-01 2012-05-01
Maliniak Kyoichi 1955-01-21 2011-02-04 2011-01-21 2012-01-21
Preusig Anneke 1953-04-20 2011-02-04 2011-04-20 2012-04-20
Zielinski Tzvetan 1957-05-23 2011-02-04 2011-05-23 2012-05-23
Kalloufi Saniya 1958-02-19 2011-02-04 2011-02-19 2012-02-19
Peac Sumant 1952-04-19 2011-02-04 2011-04-19 2012-04-19
Pivetu Duangkaew 1963-06-01 2011-02-04 2011-06-01 2012-06-01
David Jiang 1972-02-29 2011-02-04 2011-03-01 2012-02-29

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

表 2-6 最后得到的查询结果
Name BirthDay Birth_Day
Facello Georgi 1953-09-02 2011-09-02
Simmel Bezalel 1964-06-02 2011-06-02
Bamford Parto 1959-12-03 2011-12-03
Koblick Chirstian 1954-05-01 2011-05-01
Maliniak Kyoichi 1955-01-21 2012-01-21
Preusig Anneke 1953-04-20 2011-04-20
Zielinski Tzvetan 1957-05-23 2011-05-23
Kalloufi Saniya 1958-02-19 2011-02-19
Peac Sumant 1952-04-19 2011-04-19
Piveteau Duangkaew 1963-06-01 2011-06-01
David Jiang 1972-02-29 2011-03-01

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

您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:2.3.4 与日期和时间相关的函数
下一篇:2.4.2 重叠问题
相关文章
图文推荐
排行
热门
最新书评
特别推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站