查询不存在对应行时能否返回NULL？指定日期范围查询如何补全空值

实现思路 #

核心是先生成目标时间范围内的连续日期全量序列，再用这个序列作为主表和你的业务数据表做左连接，没有匹配到的日期对应的业务字段自然就会返回NULL。

通用操作步骤 #

• 第一步：生成最近10年的连续日期序列，覆盖你要查询的所有日期节点
• 第二步：将连续日期表作为左表，和原有业务表关联，关联条件为日期相等，原有查询里的其他业务过滤条件要放到关联的ON子句里（不要放到WHERE里，否则会过滤掉没有匹配的日期，变回内连接的效果）
• 第三步：查询时取连续日期表的日期，以及业务表的目标字段即可

不同数据库的实现示例 #

MySQL 8.0+/通用支持递归CTE的数据库

-- 调整递归深度，10年约3650天，设置为4000足够
SET cte_max_recursion_depth = 4000;

WITH RECURSIVE date_series AS (
    -- 起始日期：当前日期往前推10年
    SELECT DATE_SUB(CURDATE(), INTERVAL 10 YEAR) AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_series
    WHERE date_val < CURDATE()
)
SELECT 
    ds.date_val,
    t.target_column -- 没有匹配的行这里会自动返回NULL
FROM date_series ds
LEFT JOIN your_table t 
    ON ds.date_val = t.record_date
    -- 原有查询的其他过滤条件都写在这里，例如 t.category = 'xxx'
ORDER BY ds.date_val;

PostgreSQL

PostgreSQL内置generate_series函数，无需递归即可生成连续序列：

SELECT 
    ds.date_val::DATE,
    t.target_column
FROM generate_series(
    CURRENT_DATE - INTERVAL '10 years',
    CURRENT_DATE,
    INTERVAL '1 day'
) AS ds(date_val)
LEFT JOIN your_table t ON ds.date_val::DATE = t.record_date;

SQL Server

WITH date_series AS (
    SELECT DATEADD(YEAR, -10, GETDATE()) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM date_series
    WHERE date_val < GETDATE()
)
SELECT 
    CONVERT(DATE, ds.date_val) AS date_val,
    t.target_column
FROM date_series ds
LEFT JOIN your_table t ON CONVERT(DATE, t.record_date) = CONVERT(DATE, ds.date_val)
OPTION (MAXRECURSION 0); -- 解除默认递归100层的限制

注意事项 #

• 如果查询频率很高，建议预先构建专门的日期维度表存储连续日期，查询时直接关联即可，不需要每次动态生成，性能更优
• 如果需要按周、月维度聚合，只需要调整日期序列的生成步长和关联逻辑即可

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