SQLite:为范围(系统年份+9)内缺失的年份生成行

lzfw57am  于 2023-01-26  发布在  SQLite
关注(0)|答案(2)|浏览(149)

我有一个SQLite 3.38.2表,其中包含特定年份的行:

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

| 年份|金额|
| - ------|- ------|
| 小行星2024|一百|
| 小行星2025|二百|
| 小行星2025|三百|
| 二○二六|四百|
| 小行星2027|五百|
| 二○二八|六百|
| 二○二八|七百|
| 二○二八|八百|
| 二○二九|九百|
| 小行星2031|一百|
这是Demo
我希望在此范围内每年至少有一行:system year + 9。换句话说,我希望从当前年份(当前为2023年)开始,有10年的行。
但是,我在某些年份会遗漏一些行:2023年、2030年和2032年。因此,我希望为这些缺失的年份生成填充行。填充行的amount应为null
它看起来像这样:
| 年份|金额|
| - ------|- ------|
| 二○二三|- -填料|
| 小行星2024|一百|
| 小行星2025|二百|
| 小行星2025|三百|
| 二○二六|四百|
| 小行星2027|五百|
| 二○二八|六百|
| 二○二八|七百|
| 二○二八|八百|
| 二○二九|九百|
| 小行星2030|- -填料|
| 小行星2031|一百|
| 二○三二|- -填料|
在SQLite查询中,如何选择行并生成10年范围内的填充行?

    • 编辑:**我不希望在查询或表中手动创建年份列表。我希望在查询中创建动态范围。
du7egjpx

du7egjpx1#

您可以使用日历表方法,该方法要求保留一个包含要在报表中显示的所有年份的表。将此日历表与当前表左联接可获得所需结果。

WITH years AS (
    SELECT 2023 AS YEAR_ UNION ALL
    SELECT 2024 UNION ALL
    ...
    SELECT 2040
)

SELECT y.YEAR_, d.AMOUNT
FROM years y
LEFT JOIN data d
    ON d.YEAR_ = y.YEAR_
WHERE d.YEAR_ BETWEEN SYSYEAR AND SYSYEAR + 9
ORDER BY d.YEAR_;

请注意,在实践中,您可以创建一个包含未来一百年的真实表格,而不是使用上面的CTE years

qni6mghb

qni6mghb2#

还有一种选择是使用递归方法来生成N个日期:

WITH RECURSIVE cte AS (
     SELECT 2023 AS year_
  
     UNION ALL
   
     SELECT year_ + 1
     FROM cte
     WHERE year_ < 2023 + (10) -1
)
SELECT cte.year_, data.amount
FROM      cte
LEFT JOIN data
       ON cte.year_ = data.year_

检查here演示。

相关问题