如何添加将按正确顺序显示的日期(月)，现在它在递归CTESQL中倒退

Question

with recursive cte (id, qty) (
select id, qty from mytable
union all
select id, qty - 1 from cte where qty > 1
)
select id from cte order by id

问题：

如何添加将按正确顺序显示的日期(月(，现在它在递归CTE中向后？

with recursive cte (id, qty, Forecast_date) 
( 
select id, qty, StartDate from mytable 
union all 
select id, qty - 1, eomonth(dateadd(month, 1,Forecast_date)) from cte where qty > 1 ) 
select id,Forecast_date from cte order by id

输出：

9/30/2023   0
8/31/2023   1
7/31/2023   2
6/30/2023   3
5/31/2023   4
4/30/2023   5
3/31/2023   6
2/28/2023   7
1/31/2023   8
12/31/2022  9
11/30/2022  10
10/31/2022  11
9/30/2022   12
8/31/2022   13
7/31/2022   14
6/30/2022   15
5/31/2022   16
...

预计增量从2022年5月31日开始，而不是从2023-09-30开始，这应该是最后一次记录

要点：

Id  Forecast_date  qty
1   5/31/2022      0
2   6/30/2022      1

Answer 1

减去种子中的月数选择：

with recursive cte (id, qty, Forecast_date) 
(
select id, qty, eomonth(dateadd(month, -qty,StartDate)) 
from mytable 
union all 
select id, qty - 1, eomonth(dateadd(month, 1,Forecast_date)) 
from cte 
where qty >= 1 
) 
select * 
from cte 
order by id, qty

或者添加另一列，从数量0开始，向上计数而不是向下计数：

with recursive cte (id, qty, Forecast_date, stopat)
( 
select id, 0, StartDate, qty
from mytable 
union all 
select id, qty + 1, eomonth(dateadd(month, 1,Forecast_date)), stopat
from cte 
where qty < stopat 
) 
select id, qty, Forecast_date
from cte 
order by id, qty

Answer 2

从生成递增序列开始，然后在外部查询中进行计算可能会更简单：

with recursive cte (id, qty, forecast_date, lvl) (
select id, qty, datefromparts(year(forecast_date), month(forecast_date), 1), 0 lvl from mytable
union all
select id, qty, forecast_date, lvl + 1 from cte where lvl < qty
)
select 
id,
qty - lvl as dty,
eomonth(dateadd(month, -lvl, forecast_date)) new_forecast_date
from cte
order by id, new_forecast_date

月末计算很棘手：你不能通过添加月份从一个月末跳到另一个月末，因为并非所有月份的天数都相同。因此，查询使用开始月份的开始进行计算，然后使用eomonth()。