在学习MySQL的时候,一个较为经典的SQL编程题目就是生日问题,已知某个用户的出生日期和当前日期,计算他近期的生日。

一般须要考虑两个问题

  • 闰年2月是29天
  • 今年的生日是否过完

比如:某人的生日是1992年2月29日,当前若当前日期是2004年1月2日,那么他的近期生日是2004年2月29日;若当前日期是2004年3月3日,那么他近期的生日则是2005年3月1日。

如今,利用SQL编程解决这个求生日的问题。

创建employees表

Create Table: 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=latin1

再插入一些数据

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

查看表中现有数据,然后依据出生日期,以及当前日期(2014-10-31)计算近期生日日期。

SQL编程之生日问题-LMLPHP

编写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(cur) = 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;

以上这段SQL程序的输出

SQL编程之生日问题-LMLPHP

分析:

当中使用了3个子查询a、b、c

a子查询加入了一个年龄字段diff,用于子查询b,a的查询结果例如以下

SQL编程之生日问题-LMLPHP

子查询b计算了,今年的生日日期cur和明年的生日日期next,b的查询结果例如以下:

SQL编程之生日问题-LMLPHP

注意:这里对于闰月的处理是2月28日(依照法律规定,闰年29日的生日在非闰年应该为3月1日),所以子查询c用于处理闰月的问题,c的查询结果例如以下:

SQL编程之生日问题-LMLPHP

最后,须要推断当前日期是否大于今年的生日(今年是否过完了生日),然后选取近期的生日,最后的答案是:

SQL编程之生日问题-LMLPHP

05-11 17:10