sql - SQL中的迭代函数，postgres

Question

我有一个巨大的脚本，我想以迭代方式(while 或 for 循环)创建它，因此它变得易于浏览并且更短。它在 SQL 中应该是可行的，但到目前为止我还没有成功。为了让它发挥作用，我现在所做的是将很多选择联合在一起制作一张 table 。

我想遍历这些年，所以当年份低于 2017 时执行函数，其中的年份作为变量，从 1995 年开始。

所以实际上，一个迭代函数在以下代码行中填充所有年份并将所有结果组合在一个表中:我会继续尝试自己并在取得进展时更新代码。

SELECT 
    regio, 1995 as year, sum("0") as "0", sum("1") as "1", sum("2") as "2", sum("3") as "3", sum("4") as "4", sum("5") as "5", sum("6") as "6", sum("7") as "7", sum("8") as "8", sum("9") as "9", sum("10") as "10"
FROM 
    source 
where
    year = 1995 OR "year-1" = 1995 OR "year-2" = 1995 OR "year-3" = 1995 OR "year-4" = 1995 
group by 
    regio
UNION
SELECT 
    regio, 1996 as year, sum("0") as "0", sum("1") as "1", sum("2") as "2", sum("3") as "3", sum("4") as "4", sum("5") as "5", sum("6") as "6", sum("7") as "7", sum("8") as "8", sum("9") as "9", sum("10") as "10"
FROM 
    source 
where 
    year = 1996 OR "year-1" = 1996 OR "year-2" = 1996 OR "year-3" = 1996  OR "year-4" = 1996 
group by 
    regio

Answer 1

你似乎想要:

SELECT regio, g.yyyy as year, sum("0") as "0", sum("1") as "1",
       sum("2") as "2", sum("3") as "3", sum("4") as "4",
       sum("5") as "5", sum("6") as "6", sum("7") as "7",
       sum("8") as "8", sum("9") as "9", sum("10") as "10"
FROM source CROSS JOIN
     generate_series(1995, 2017) g(yyyy)
WHERE g.yyyy IN (year, "year-1", "year-2", "year-3", "year-4")
GROUP BY regio, g.yyyy;