如何编写SQL查询遍历日期范围并返回无记录的日期？

要找出指定日期范围内无记录的日期，核心思路是先生成该范围内的所有连续日期，再排除表中已有记录的日期。针对你提到的“每日数据量大，用limit 1提升效率”的需求，可以通过NOT EXISTS子查询结合LIMIT 1快速判断某一天是否存在记录，避免扫描全量数据。

1. 生成目标日期范围内的连续日期 #

不同数据库生成连续日期的方式略有差异，以下是主流数据库的实现：

MySQL 8.0+（递归CTE） #

WITH RECURSIVE date_range AS (
    SELECT '2016-01-01' AS target_date
    UNION ALL
    SELECT DATE_ADD(target_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE target_date < '2019-12-30'
)
SELECT target_date
FROM date_range;

PostgreSQL（generate_series函数） #

SELECT generate_series(
    '2016-01-01'::DATE,
    '2019-12-30'::DATE,
    '1 day'
) AS target_date;

SQL Server（递归CTE） #

WITH date_range AS (
    SELECT CAST('2016-01-01' AS DATE) AS target_date
    UNION ALL
    SELECT DATEADD(DAY, 1, target_date)
    FROM date_range
    WHERE target_date < CAST('2019-12-30' AS DATE)
)
SELECT target_date
FROM date_range
OPTION (MAXRECURSION 0); -- 解除递归次数限制

2. 筛选无记录的日期 #

结合NOT EXISTS子查询，利用LIMIT 1（SQL Server用TOP 1）快速判断当天是否存在记录，完整查询如下：

MySQL 8.0+ 完整查询 #

WITH RECURSIVE date_range AS (
    SELECT '2016-01-01' AS target_date
    UNION ALL
    SELECT DATE_ADD(target_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE target_date < '2019-12-30'
)
SELECT target_date AS missing_date
FROM date_range
WHERE NOT EXISTS (
    SELECT 1
    FROM some_table
    WHERE some_table.date = date_range.target_date
    LIMIT 1 -- 找到一条记录就停止，提升效率
);

PostgreSQL 完整查询 #

SELECT gs.target_date AS missing_date
FROM generate_series(
    '2016-01-01'::DATE,
    '2019-12-30'::DATE,
    '1 day'
) AS gs(target_date)
WHERE NOT EXISTS (
    SELECT 1
    FROM some_table
    WHERE some_table.date = gs.target_date
    LIMIT 1
);

SQL Server 完整查询 #

WITH date_range AS (
    SELECT CAST('2016-01-01' AS DATE) AS target_date
    UNION ALL
    SELECT DATEADD(DAY, 1, target_date)
    FROM date_range
    WHERE target_date < CAST('2019-12-30' AS DATE)
)
SELECT target_date AS missing_date
FROM date_range
WHERE NOT EXISTS (
    SELECT TOP 1 1
    FROM some_table
    WHERE some_table.date = date_range.target_date
)
OPTION (MAXRECURSION 0);

3. 针对特定parser_code的情况 #

如果你的需求是找出**指定parser_code（比如pc）**无记录的日期，只需在NOT EXISTS子查询中添加parser_code = 'pc'条件即可：

-- 以MySQL为例，其他数据库类似
WHERE NOT EXISTS (
    SELECT 1
    FROM some_table
    WHERE some_table.date = date_range.target_date
      AND parser_code = 'pc'
    LIMIT 1
);

关键优化点 #

• 使用NOT EXISTS + LIMIT 1：相比LEFT JOIN后判断NULL，这种方式在找到当天第一条记录后就停止扫描，避免处理全量日数据，大幅提升查询效率。
• 确保some_table.date字段有索引：如果日期字段没有索引，即使加了LIMIT 1，查询效率也会受影响，建议给date字段（或date + parser_code组合）创建索引。

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