[SQL] 生日问题的一种解法
2018-11-01 本文已影响0人
zqq90
这是同事做分享的时候提及的一个问题, 觉得比较有意思就尝试了一下
生日问题
用一条 SQL 得出雇员们的全名，以及他的最近一次生日(如果他今年没有过生日，显示今年的生日，如果今年过生日了，显示明年的生日), 环境为 MySQL 5.7+,
表结构如下:
CREATE TABLE employees (
emp_no INT 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)
);
记录详见附录
废话少说, 上代码
虽然看着 长 实际很简单, 都是注释害的
-- 为了思路清晰, 分了很多子查询, 对单测也友好
-- 思路真的很简单, 别掉队
-- PS: 不考虑时区问题
-- PS: 不考虑跨天执行的临界情况
-- PS: 部分字段使用驼峰命名, 莫怪, 真的只是为了更清晰一些
SELECT fullname, birthday,
-- 这里就算正式开始了
CASE
-- 如果生日是 02-29 直接取下一个 02-29 就可以了
WHEN birthMonthDay = 229 THEN str_to_date(next_229, "%Y%m%d")
-- 否则, 只需要看是不是需要 +1 年: 今年的生日已经过了 +1, 否则不加
ELSE str_to_date((thisYear + if(birthMonthDay < thisMonthDay, 1, 0)) * 10000 + birthMonthDay, "%Y%m%d")
END as next_birthday
-- 到这里整体思路就已经结束了, 意外不? 简单吧? 还没完, 咱们还有几个问题没解决呢
FROM
-- 员工的基础信息拆解: 为了简化上面的操作, 非常简单, 不多解释
(
SELECT
CONCAT(first_name, ' ', last_name) as fullname, birth_date as birthday,
0 + date_format(birth_date, '%m%d') as birthMonthDay
FROM employees
) t_employees_meta
-- 接下来, 最关键的就是解决 next_229, 即: 下一个 02-29 在哪一年?
JOIN (
-- 如果可以的话, 拆成视图: CREATE VIEW t_today_meta AS
SELECT *,
-- 解决 next_229 问题:
CASE
-- 如果今天是 02-29 肯定就是今天了 (可以合并到下一条, 为了简单先剔出来)
WHEN thisMonthDay = 229 THEN thisYear * 10000 + 229
-- 如果今天小于 02-29, 且是闰年: 直接取今年的 02-29
WHEN thisMonthDay < 229 and (thisYear % 4 = 0 AND thisYear % 100 <> 0 OR thisYear % 400 = 0)
THEN thisYear * 10000 + 229
-- 否则为下一个闰年的 02-29
-- 如果下一个疑似闰年不是闰年, +4 后必然是闰年
-- PS: 不需要判断 %4, 因为候选已经满足了
ELSE if(nextLeapYearCandidate % 100 <> 0 OR nextLeapYearCandidate % 400 = 0,
nextLeapYearCandidate, nextLeapYearCandidate +4) * 10000 + 229
END as next_229
FROM (
SELECT
today,
year(today) as thisYear,
0 + date_format(today, '%m%d') as thisMonthDay,
-- 下一个疑似闰年, 只满足 4 的倍数, 先不管其他 (为了上层更简单, 这里先计算好这个候选)
year(today) + 4 - year(today) % 4 as nextLeapYearCandidate
FROM (
-- 为了方便测试, 写成子查询, 可以指定某天
SELECT curdate()
-- SELECT str_to_date(20181101, "%Y%m%d")
-- SELECT str_to_date(20200228, "%Y%m%d")
-- SELECT str_to_date(20200229, "%Y%m%d")
-- SELECT str_to_date(20980220, "%Y%m%d")
-- SELECT str_to_date(20980301, "%Y%m%d")
-- SELECT str_to_date(19970301, "%Y%m%d")
-- SELECT str_to_date(20000229, "%Y%m%d")
as today
) t_today_source
) t_today
) t_today_meta
执行结果详见附录
其他思考
- 优化点: 将解决
next_229问题 的部分拆成视图 (VIEW), 可以实现共用, 题目要求 "用一条 SQL" 所以就没这么做
附录:
员工数据:
INSERT INTO `employees` VALUES (10001,'1953-02-28','Georgi','Facello','M','1986-06-26'),
(10002,'1964-02-29','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-11-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
数据来源: https://github.com/datacharmer/test_db, 有稍许修改
查询结果
- 日期: 20181101
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 2019-02-28 |
| Bezalel Simmel | 1964-02-29 | 2020-02-29 |
| Parto Bamford | 1959-12-03 | 2018-12-03 |
| Chirstian Koblick | 1954-05-01 | 2019-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 2019-01-21 |
| Anneke Preusig | 1953-11-20 | 2018-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 2019-05-23 |
+-------------------+------------+---------------+
- 日期: 20200228
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 2020-02-28 |
| Bezalel Simmel | 1964-02-29 | 2020-02-29 |
| Parto Bamford | 1959-12-03 | 2020-12-03 |
| Chirstian Koblick | 1954-05-01 | 2020-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 2021-01-21 |
| Anneke Preusig | 1953-11-20 | 2020-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 2020-05-23 |
+-------------------+------------+---------------+
- 日期: 20200229
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 2021-02-28 |
| Bezalel Simmel | 1964-02-29 | 2020-02-29 |
| Parto Bamford | 1959-12-03 | 2020-12-03 |
| Chirstian Koblick | 1954-05-01 | 2020-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 2021-01-21 |
| Anneke Preusig | 1953-11-20 | 2020-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 2020-05-23 |
+-------------------+------------+---------------+
- 日期: 20980220
取这个 test case 是因为 2100 年不是闰年, 看是否真的跳过了
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 2098-02-28 |
| Bezalel Simmel | 1964-02-29 | 2104-02-29 |
| Parto Bamford | 1959-12-03 | 2098-12-03 |
| Chirstian Koblick | 1954-05-01 | 2098-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 2099-01-21 |
| Anneke Preusig | 1953-11-20 | 2098-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 2098-05-23 |
+-------------------+------------+---------------+
- 日期: 20980301
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 2099-02-28 |
| Bezalel Simmel | 1964-02-29 | 2104-02-29 |
| Parto Bamford | 1959-12-03 | 2098-12-03 |
| Chirstian Koblick | 1954-05-01 | 2098-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 2099-01-21 |
| Anneke Preusig | 1953-11-20 | 2098-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 2098-05-23 |
+-------------------+------------+---------------+
- 日期: 19970301
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 1998-02-28 |
| Bezalel Simmel | 1964-02-29 | 2000-02-29 |
| Parto Bamford | 1959-12-03 | 1997-12-03 |
| Chirstian Koblick | 1954-05-01 | 1997-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 1998-01-21 |
| Anneke Preusig | 1953-11-20 | 1997-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 1997-05-23 |
+-------------------+------------+---------------+
- 日期: 20000229
+-------------------+------------+---------------+
| fullname | birthday | next_birthday |
+-------------------+------------+---------------+
| Georgi Facello | 1953-02-28 | 2001-02-28 |
| Bezalel Simmel | 1964-02-29 | 2000-02-29 |
| Parto Bamford | 1959-12-03 | 2000-12-03 |
| Chirstian Koblick | 1954-05-01 | 2000-05-01 |
| Kyoichi Maliniak | 1955-01-21 | 2001-01-21 |
| Anneke Preusig | 1953-11-20 | 2000-11-20 |
| Tzvetan Zielinski | 1957-05-23 | 2000-05-23 |
+-------------------+------------+---------------+
