SQLite如何为系统年份+9范围内的缺失年份生成填充行？

我有一个SQLite 3.38.2的表，包含特定年份的数据，示例数据的SQL如下：

WITH data (year_, amount) AS (
    VALUES 
        (2024, 100),
        (2025, 200),
        (2025, 300),
        (2026, 400),
        (2027, 500),
        (2028, 600),
        (2028, 700),
        (2028, 800),
        (2029, 900),
        (2031, 100)
)
SELECT * FROM data;

对应的查询结果：

需求说明 #

需要覆盖当前年份到当前年份+9的10年范围（当前年份为2023），确保每个年份至少有一行数据。目前缺失2023、2030、2032年的行，需为这些缺失年份生成填充行，填充行的amount字段实际为NULL（最终结果中--filler仅为示意展示）。期望的最终结果如下：

解决方案 #

通过递归CTE动态生成年份范围，无需手动创建年份列表或额外表格，再结合左连接和UNION ALL实现数据合并：

WITH data (year_, amount) AS (
    VALUES 
        (2024, 100),
        (2025, 200),
        (2025, 300),
        (2026, 400),
        (2027, 500),
        (2028, 600),
        (2028, 700),
        (2028, 800),
        (2029, 900),
        (2031, 100)
),
-- 动态生成当前年份到当前年份+9的连续年份
year_range AS (
    SELECT strftime('%Y', 'now') + 0 AS year_
    UNION ALL
    SELECT year_ + 1 FROM year_range WHERE year_ < strftime('%Y', 'now') + 9
),
-- 合并原始数据与缺失年份的填充行
combined AS (
    -- 保留所有原始数据行
    SELECT d.year_, d.amount
    FROM data d
    UNION ALL
    -- 为缺失年份添加填充行（amount为NULL）
    SELECT yr.year_, NULL AS amount
    FROM year_range yr
    LEFT JOIN data d ON yr.year_ = d.year_
    WHERE d.year_ IS NULL
)
-- 按年份排序，将NULL显示为--filler（可根据需求调整）
SELECT 
    year_, 
    CASE WHEN amount IS NULL THEN '--filler' ELSE amount END AS amount
FROM combined
ORDER BY year_;

代码说明 #

1. year_range递归CTE：

   • strftime('%Y', 'now') + 0获取当前系统年份并转为数值类型
   • 通过递归生成从当前年份到当前年份+9的所有连续年份，确保覆盖10年范围

2. combined数据合并：

   • 先保留原始数据的所有行
   • 再通过左连接找出year_range中没有对应数据的年份，为这些年份生成amount为NULL的填充行，通过UNION ALL合并到结果中

3. 最终输出处理：

   • 使用CASE语句将NULL值转换为--filler用于展示（若不需要文本展示，直接输出amount即可）
   • 按年份排序，保证结果顺序符合预期

固定年份范围的调整 #

如果不需要动态获取当前年份，而是固定使用2023-2032的范围，只需修改year_range部分：

year_range AS (
    SELECT 2023 AS year_
    UNION ALL
    SELECT year_ + 1 FROM year_range WHERE year_ < 2032
)

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