如何在查询结果集中追加虚拟记录（补全缺失日期行）

核心思路是先生成目标日期范围内的完整日期序列，再与你的原查询结果做左连接，自动补全缺失日期的虚拟记录，其他字段可设为自定义默认值。以下是主流数据库的具体实现：

MySQL 实现 #

使用递归CTE生成日期序列，再关联原查询：

WITH RECURSIVE date_range AS (
    -- 替换为你的起始日期
    SELECT '2024-05-01' AS date_col
    UNION ALL
    SELECT DATE_ADD(date_col, INTERVAL 1 DAY)
    FROM date_range
    -- 替换为你的结束日期
    WHERE date_col < '2024-05-10'
)
SELECT 
    dr.date_col,
    -- 把other_column换成你的实际字段，'虚拟值'替换为你需要的默认内容
    COALESCE(t.other_column, '虚拟值') AS other_column
FROM date_range dr
LEFT JOIN (
    -- 替换成你的原查询语句
    SELECT date_col, other_column FROM your_table
) t ON dr.date_col = t.date_col
ORDER BY dr.date_col;

PostgreSQL 实现 #

利用generate_series快速生成日期序列：

SELECT 
    dr.date_col,
    COALESCE(t.other_column, '虚拟值') AS other_column
FROM generate_series(
    -- 替换为起始、结束日期
    '2024-05-01'::DATE,
    '2024-05-10'::DATE,
    '1 day'::INTERVAL
) dr(date_col)
LEFT JOIN (
    -- 替换为你的原查询
    SELECT date_col, other_column FROM your_table
) t ON dr.date_col = t.date_col
ORDER BY dr.date_col;

SQL Server 实现 #

递归CTE生成日期序列，注意大范围日期需开启MAXRECURSION：

WITH date_range AS (
    SELECT CAST('2024-05-01' AS DATE) AS date_col
    UNION ALL
    SELECT DATEADD(DAY, 1, date_col)
    FROM date_range
    WHERE date_col < CAST('2024-05-10' AS DATE)
)
SELECT 
    dr.date_col,
    ISNULL(t.other_column, '虚拟值') AS other_column
FROM date_range dr
LEFT JOIN (
    -- 替换为你的原查询
    SELECT date_col, other_column FROM your_table
) t ON dr.date_col = t.date_col
ORDER BY dr.date_col
OPTION (MAXRECURSION 0); -- 日期范围超过100天必须添加

关键注意事项 #

• 若不想硬编码日期范围，可动态获取原查询的最小/最大日期，比如把起始日期换成(SELECT MIN(date_col) FROM your_table)，结束日期换成(SELECT MAX(date_col) FROM your_table)
• COALESCE/ISNULL用于将缺失字段替换为默认值，可根据需求设为NULL、0、'无数据'等
• 原查询需包裹在子查询中，确保与日期序列的左连接能保留所有日期，包括原结果中缺失的那些

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