Oracle 18c如何生成指定年份范围的缺失年份填充行

要动态生成目标年份范围并填充缺失行，可以通过生成年份序列+左连接原数据的方式实现，具体步骤如下：

1. 动态生成10年范围的年份序列 #

利用Oracle的CONNECT BY子句，基于当前年份（EXTRACT(YEAR FROM SYSDATE)）生成从当前年到当前年+9的所有年份，无需手动维护年份列表：

WITH year_range AS (
    SELECT EXTRACT(YEAR FROM SYSDATE) + LEVEL - 1 AS year_
    FROM dual
    CONNECT BY LEVEL <= 10
)
SELECT year_ FROM year_range;

如果偏好递归CTE写法，也可以用：

WITH year_range AS (
    SELECT EXTRACT(YEAR FROM SYSDATE) AS year_
    FROM dual
    UNION ALL
    SELECT year_ + 1
    FROM year_range
    WHERE year_ < EXTRACT(YEAR FROM SYSDATE) + 9
)
SELECT year_ FROM year_range;

2. 关联原数据并填充缺失行 #

将生成的年份序列与原数据表做左外连接，确保每个年份都被保留：原数据中存在的年份会带出对应amount值，缺失年份则amount自动为NULL。结合你的示例数据，完整SQL如下：

WITH data (year_, amount) AS (
    -- 模拟你的原始数据表
    SELECT 2024, 100 FROM dual UNION ALL
    SELECT 2025, 200 FROM dual UNION ALL
    SELECT 2025, 300 FROM dual UNION ALL
    SELECT 2026, 400 FROM dual UNION ALL
    SELECT 2027, 500 FROM dual UNION ALL
    SELECT 2028, 600 FROM dual UNION ALL
    SELECT 2028, 700 FROM dual UNION ALL
    SELECT 2028, 800 FROM dual UNION ALL
    SELECT 2029, 900 FROM dual UNION ALL
    SELECT 2031, 100 FROM dual
),
year_range AS (
    -- 动态生成当前年到当前年+9的10年范围
    SELECT EXTRACT(YEAR FROM SYSDATE) + LEVEL - 1 AS year_
    FROM dual
    CONNECT BY LEVEL <= 10
)
-- 左连接保留所有年份，填充缺失行
SELECT 
    yr.year_,
    d.amount
FROM year_range yr
LEFT JOIN data d ON yr.year_ = d.year_
ORDER BY yr.year_;

关键细节说明 #

• EXTRACT(YEAR FROM SYSDATE)：自动获取当前系统年份，确保年份范围随时间动态更新，无需手动调整。
• LEFT JOIN：强制保留年份序列中的所有条目，原数据无对应行时amount字段自动显示为NULL，满足“每个年份至少一行”的要求。
• ORDER BY yr.year_：保证结果按年份升序排列，与你给出的预期格式一致。

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