如何通过单次SQL查询获取指定日期区间内无记录的日期？

表结构与示例数据 #

表A的结构及数据如下：

需求说明 #

查询指定dateStart到dateEnd区间内，表A中无记录的日期。例如当dateStart='2025-01-01'、dateEnd='2025-01-09'时，期望返回结果：

解决方案 #

核心思路是先生成目标区间内的所有连续日期，再将该日期集合与表A做左连接，筛选出表A中无匹配记录的日期。以下是主流数据库的实现方式：

1. MySQL（8.0+版本） #

利用递归CTE生成连续日期：

WITH RECURSIVE date_range AS (
    SELECT '2025-01-01' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2025-01-09'
)
SELECT dr.date
FROM date_range dr
LEFT JOIN 表A a ON dr.date = a.date
WHERE a.date IS NULL
ORDER BY dr.date;

2. PostgreSQL #

使用generate_series函数快速生成日期序列，搭配EXCEPT筛选：

SELECT generate_series('2025-01-01'::date, '2025-01-09'::date, '1 day') AS date
EXCEPT
SELECT date FROM 表A
ORDER BY date;

也可以用左连接方式实现：

SELECT gs.date
FROM generate_series('2025-01-01'::date, '2025-01-09'::date, '1 day') gs(date)
LEFT JOIN 表A a ON gs.date = a.date
WHERE a.date IS NULL
ORDER BY gs.date;

3. SQL Server #

通过递归CTE生成日期序列，注意长区间需开启最大递归限制：

WITH date_range AS (
    SELECT CAST('2025-01-01' AS DATE) AS date
    UNION ALL
    SELECT DATEADD(DAY, 1, date)
    FROM date_range
    WHERE date < CAST('2025-01-09' AS DATE)
)
SELECT dr.date
FROM date_range dr
LEFT JOIN 表A a ON dr.date = a.date
WHERE a.date IS NULL
ORDER BY dr.date
OPTION (MAXRECURSION 0);

4. Oracle #

使用CONNECT BY生成连续日期，搭配MINUS筛选无记录日期：

SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') + LEVEL - 1 AS date
FROM dual
CONNECT BY TO_DATE('2025-01-01', 'YYYY-MM-DD') + LEVEL - 1 <= TO_DATE('2025-01-09', 'YYYY-MM-DD')
MINUS
SELECT date FROM 表A
ORDER BY date;

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