如何在MySQL中根据指定日期范围获取表中缺失日期

针对你这个需求——从records表中找出指定日期区间内缺失的日期，我这里给出两种实用的解决方案，分别适配不同版本的MySQL：

方法一：使用递归CTE（MySQL 8.0+ 适用） #

如果你的MySQL版本是8.0或以上，递归CTE是最简洁的实现方式，不需要额外创建辅助表：

WITH RECURSIVE date_range AS (
    SELECT '2018-03-23' AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < '2018-03-30'
)
SELECT DAY(date_val) AS missing_days
FROM date_range
WHERE date_val NOT IN (SELECT dates FROM records);

代码说明： #

1. 递归CTE date_range 会生成从起始日期2018-03-23到结束日期2018-03-30的所有连续日期；
2. 通过NOT IN子句筛选出不在records表dates字段中的日期；
3. 最后用DAY()函数提取日期的「日」部分，得到你预期的结果：25、26、29。

方法二：数字辅助表（适配MySQL 5.x及更早版本） #

如果你的MySQL版本不支持CTE，可以借助数字辅助表来生成连续日期：

步骤1：创建临时数字表（可选，可复用） #

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30);

步骤2：查询缺失日期 #

SELECT DAY(DATE_ADD('2018-03-23', INTERVAL n DAY)) AS missing_days
FROM nums
WHERE DATE_ADD('2018-03-23', INTERVAL n DAY) <= '2018-03-30'
AND DATE_ADD('2018-03-23', INTERVAL n DAY) NOT IN (SELECT dates FROM records);

替代方案：无需临时表，直接用子查询生成数字序列 #

如果不想创建临时表，也可以直接用UNION ALL拼接数字生成序列：

SELECT DAY(DATE_ADD('2018-03-23', INTERVAL n DAY)) AS missing_days
FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
    UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
    UNION ALL SELECT 30
) AS nums
WHERE DATE_ADD('2018-03-23', INTERVAL n DAY) <= '2018-03-30'
AND DATE_ADD('2018-03-23', INTERVAL n DAY) NOT IN (SELECT dates FROM records);

代码说明： #

• 数字序列提供了从0到30的增量值，足够覆盖一个月内的所有日期；
• 通过DATE_ADD函数基于起始日期生成连续日期，再筛选出区间内不在原表中的日期，最后提取「日」部分。

内容的提问来源于stack exchange，提问作者Srinu Chilukuri