[SQL] 生日问题的一种解法

作者: zqq90 | 来源:发表于2018-11-01 15:49 被阅读0次

这是同事做分享的时候提及的一个问题, 觉得比较有意思就尝试了一下

生日问题

用一条 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    |
+-------------------+------------+---------------+

网友评论

本文标题：[SQL] 生日问题的一种解法

本文链接：https://www.haomeiwen.com/subject/ogsxxqtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！