如何在MySQL中查询指定日期范围内无销售记录的日期

嘿，这个需求其实很好解决，核心思路就是先把目标日期范围内的所有日期都列出来，再排除掉已经有销售记录的那些日期就行。下面针对几种主流数据库给你具体的实现方案：

MySQL 8.0及以上版本支持递归CTE，用这个方法生成日期序列最简洁：

WITH RECURSIVE date_range AS (
    SELECT '2019-01-01' AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < '2019-01-31'
)
SELECT dr.date_val AS missing_date
FROM date_range dr
LEFT JOIN Sales s ON dr.date_val = s.for_date
WHERE s.id IS NULL
ORDER BY dr.date_val;

解释：递归CTE从起始日期2019-01-01开始，每天自动加1天，直到达到结束日期2019-01-31；之后用左连接关联Sales表，筛选出关联不上（也就是id为NULL）的日期，就是没有销售记录的日期。

如果你的MySQL版本低于8.0，没法用递归CTE，可以手动生成一个数字辅助表来构造日期：

SELECT 
    DATE_ADD('2019-01-01', INTERVAL n.n DAY) AS missing_date
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
) n
LEFT JOIN Sales s ON DATE_ADD('2019-01-01', INTERVAL n.n DAY) = s.for_date
WHERE s.id IS NULL
AND DATE_ADD('2019-01-01', INTERVAL n.n DAY) <= '2019-01-31'
ORDER BY missing_date;

PostgreSQL自带的generate_series函数可以直接生成日期序列，写法非常简洁：

SELECT generate_series('2019-01-01'::date, '2019-01-31'::date, '1 day'::interval)::date AS missing_date
EXCEPT
SELECT for_date FROM Sales
ORDER BY missing_date;

或者用左连接的方式，逻辑和MySQL一致：

SELECT dr.date_val AS missing_date
FROM generate_series('2019-01-01'::date, '2019-01-31'::date, '1 day') dr(date_val)
LEFT JOIN Sales s ON dr.date_val = s.for_date
WHERE s.id IS NULL
ORDER BY dr.date_val;

解释：generate_series直接生成指定范围内的所有日期，然后用EXCEPT关键字排除掉Sales表中已有的日期，剩下的就是目标结果。

SQL Server同样可以用递归CTE生成日期序列，注意要加上MAXRECURSION选项（因为默认递归次数限制是100，超过的话会报错）：

WITH date_range AS (
    SELECT CAST('2019-01-01' AS DATE) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM date_range
    WHERE date_val < CAST('2019-01-31' AS DATE)
)
SELECT dr.date_val AS missing_date
FROM date_range dr
LEFT JOIN Sales s ON dr.date_val = s.for_date
WHERE s.id IS NULL
ORDER BY dr.date_val
OPTION (MAXRECURSION 0);

如果你不想用递归，也可以利用系统表生成数字来构造日期：

SELECT 
    DATEADD(DAY, n.number, '2019-01-01') AS missing_date
FROM master.dbo.spt_values n
WHERE n.type = 'P'
AND DATEADD(DAY, n.number, '2019-01-01') <= '2019-01-31'
LEFT JOIN Sales s ON DATEADD(DAY, n.number, '2019-01-01') = s.for_date
WHERE s.id IS NULL
ORDER BY missing_date;

不管用哪种数据库，本质都是两步：

1. 生成目标日期范围内的完整日期序列：根据数据库特性选择递归CTE、内置函数或者辅助表的方式；
2. 排除已有记录的日期：通过左连接筛选NULL值，或者用EXCEPT/EXCEPT ALL关键字来实现反匹配。

内容的提问来源于stack exchange，提问作者Rajan Acharya