带有CTE的T-SQL窗口函数，使用先前计算的值

Question

我尝试使用以下CTE查询计算引用先前结果值的Result字段中的值:

WITH cteA (N, val1) AS (
SELECT 1, 5.1 UNION
SELECT 2, 6.5 UNION
SELECT 3, 7.5 UNION
SELECT 4, 4.6 UNION
SELECT 5, 3.2
), cteB AS (
SELECT *
, val1 / LAG(val1) OVER (ORDER BY N) val2
, (CASE N WHEN 1 THEN 100 END) result
FROM cteA
)
SELECT *
FROM cteB

在Result字段返回意想不到的NULL值:

https://i.stack.imgur.com/oeMcO.png

我需要帮助来获得期望值，而不是NULLs，如下所示:

https://i.stack.imgur.com/XprEW.png

Answer 1

您需要使用递归cte

with
cteR AS
(
SELECT  N, val1, 
val2   = convert(decimal(20,4), NULL), 
result = convert(decimal(20,4), 100)
FROM    cteA
WHERE   N   = 1
UNION ALL
SELECT  a.N, a.val1, 
val2   = convert(decimal(20,4), a.val1 / r.val1), 
result = convert(decimal(20,4), a.val1 / r.val1 * r.result)
FROM    cteR r
INNER JOIN cteA a   on  r.N = a.N - 1
)
SELECT  *
FROM    cteR