sql - 创建作为常数值的季节和年份列表

Question

我想生成一个季节和年份的列表，即(FA2019、WI2019、SP2019、SU2019) 当前年份 + future 3 年。然后将其输入到 WHERE 子句中的另一个查询中。

现在我每个季节都有 4 条语句，我只是将它们 UNION 在一起。有没有更短的方法来做到这一点？

SELECT 'FA' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

UNION

SELECT 'WI' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

UNION

SELECT 'SP' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

UNION

SELECT 'SU' || YYYY AS SSYYYY FROM (
SELECT to_number(to_char(SYSDATE, 'YYYY'))+LEVEL-1 AS YYYY FROM dual
CONNECT BY LEVEL <= 4)

Answer 1

您可以使用 CROSS JOIN 两个 CONNECT BY 查询，例如:

SELECT s.SS || y.YYYY AS SSYYYY
FROM 
(
    SELECT to_number(to_char(SYSDATE, 'YYYY')) + LEVEL - 1 AS YYYY 
    FROM dual 
    CONNECT BY LEVEL <= 4
) y
CROSS JOIN (
    SELECT DECODE(level, 1, 'FA', 2, 'WI', 3, 'SP', 4, 'SU') AS SS 
    FROM dual 
    CONNECT BY LEVEL <= 4
) s
ORDER BY y.YYYY, s.SS

返回:

| SSYYYY || :----- || FA2019 || SP2019 || SU2019 || WI2019 || FA2020 || SP2020 || SU2020 || WI2020 || FA2021 || SP2021 || SU2021 || WI2021 || FA2022 || SP2022 || SU2022 || WI2022 |

Demo on DB Fiddle