sql - how can I generate this list of data with a SQL query?

Question

year	month
2021	1
2021	2
2021	3
2021	4
2021	5
2021	6
2021	7
2021	8
2021	9
2021	10
2021	11
2021	12
2022	1
2022	2
2022	3
2022	4
2022	5
2022	6
2022	7
2022	8
2022	9
2022	10
2022	11
2022	12

I can get one column fine, for example
SELECT * FROM generate_series(1,12) as month but I can't find how to get another column next to it that is generated rather than joined to an actual table.

标签： sqlpostgresqlsubquery

Answer 1

You may use a cross join:

SELECT y.year, m.month
FROM (SELECT 2020 + generate_series(1, 2) AS year) y
CROSS JOIN (SELECT generate_series(1, 12) AS month) m
ORDER BY y.year, m.month;

Demo