在Postgresql中创建一个过去12个月的表

Question

我正在尝试创建一个表，看起来像这样(过去12个月的表)

month, year
10, 2016
9, 2016
8, 2016
7, 2016
6, 2016
5, 2016
4, 2016
3, 2016
2, 2016
1, 2016
12, 2015
11, 2015

我的代码看起来像这样:

select date_part('month', current_date) as order_month,
select date_part('year', current_date) as order_year
union all
select date_part('month', current_date - interval '1 month') as order_month,
select date_part('year', current_date - interval '1 month') as order_year
union all
...

是否有更简洁的方式来写它，而不是使用11个联合?

Answer 1

generate_series(start, stop, step)将非常有用，

SELECT
    EXTRACT('month' FROM d) AS month,
    EXTRACT('year' FROM d) AS year
FROM
    GENERATE_SERIES(
        now(),
        now() - interval '12 months',
        interval '-1 month'
    ) AS d
;