mysql - 用未来年份创建 @X 行

Question

我正在尝试创建一个 SQL 来显示未来几年的 @X 行：

year(now())+1
year(now())+2
year(now())+3
...

@X 是用户定义的变量。

我只是来使用联合选择，但它看起来很讨厌并且适用于相当低的@x：

set @x:=3;
select year(now())+1 as year
union select if(@x>1, year(now())+2,null) as year
union select if(@x>2, year(now())+3,null) as year
union select if(@x>3, year(now())+4,null) as year;

标签： mysqlunionuser-defined

Answer 1

那将是自 MySQL 8 以来可用的递归 CTE 的领域。

SET @x = 5;

WITH RECURSIVE
years
AS
(
SELECT year(now()) year
UNION ALL
SELECT year + 1
       FROM years
       WHERE year + 1 - year(now()) <= @x
)
SELECT *
       FROM years;

db<>小提琴