如何獲得以下查詢給我過去 N 年的相同日期？
declare @step int = 0;
declare @datobserve date = '2021-11-03';
with dates as
(
select dateadd(year, @step, @datobserve) datobserve, @step step
union all
select dateadd(year, step, datobserve) as datobserve, step - 1
from dates
where 1=1
--and step = step 1
and step > -4
)
select * from dates
我得到的結果是：
代替：
uj5u.com熱心網友回復：
您需要在陳述句的遞回成員中進行兩項更改：
- 使用
@datobserve代替datobserve - 使用
step - 1代替step
declare @step int = 0;
declare @datobserve date = '2021-11-03';
with dates as
(
select dateadd(year, @step, @datobserve) datobserve, @step step
union all
select dateadd(year, step - 1, @datobserve) as datobserve, step - 1
from dates
where step > -4
)
select *
from dates
結果：
datobserve step
2021-11-03 0
2020-11-03 -1
2019-11-03 -2
2018-11-03 -3
2017-11-03 -4
uj5u.com熱心網友回復：
使用臨時計數表的另一種選擇
例子
Declare @Years int = 4
Declare @Date date ='2021-11-03'
Select dateobserve = dateadd(year,N,@Date)
,Step = N
From ( Select Top (@Years 1) N=1-Row_Number() Over (Order By (Select NULL))
From master..spt_values n1
) NT
結果
dateobserve Step
2021-11-03 0
2020-11-03 -1
2019-11-03 -2
2018-11-03 -3
2017-11-03 -4
uj5u.com熱心網友回復：
當你知道它只有幾行時，比如 5，我發現不打擾遞回更簡單：
DECLARE @datobserve date = '20211103';
;WITH n(n) AS
(
SELECT n FROM (VALUES(1),(2),(3),(4),(5)) AS n(n)
)
SELECT datobserve = DATEADD(YEAR, 1-n, @datobserve), step = 1-n
FROM n
ORDER BY datobserve DESC;
如果串列更大或可變，我仍然喜歡遞回地獲取數字部分，并圍繞該輸出處理日期：
DECLARE @steps int = 5,
@datobserve date = '20211103';
;WITH n(n) AS
(
SELECT 1 UNION ALL SELECT n 1 FROM n WHERE n < @steps
)
SELECT datobserve = DATEADD(YEAR, 1-n, @datobserve), step = 1-n
FROM n
ORDER BY datobserve DESC;
- 例子@db<>fiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/349943.html
標籤：sql sql-server