如何使用递归CTE获取上一行和当前行的值？ [英] How to get previous and current row value using recursive CTE?

问题描述

请考虑以下内容

Id  Nums
1   10
2   20
3   30
4   40
5   50

预期产出

Id  CurrentValue    PreviousValue
1   10              Null
2   20              10
3   30              20
4   40              30
5   50              40

我正在尝试以下操作，但没有运气

I am trying with the below but no luck

;With Cte(Id,CurrValue,PrevValue) As
(
    Select 
        Id
        ,CurrentValue = Nums 
        ,PreviousValue = Null
        From @t Where Id = 1
    Union All
    Select
        t.Id
        ,c.CurrValue
        ,c.PrevValue
    From Cte c
    Join @t t On c.Id <= t.Id + 1

)
Select *
From Cte

需要帮助

推荐答案

这假定ID值增加，并且将l带有ID的空白

This assumes increasing ID values and will deal with gaps in ID

SELECT
   ID, 
   This.Number AS CurrentValue, 
   Prev2.Number AS PreviousValue
FROM
   myTable This
   OUTER APPLY
   (
    SELECT TOP 1
       Number
    FROM
       myTable Prev
    WHERE
       Prev.ID < This.ID  -- change to number if you want
    ORDER BY
       Prev.ID DESC
   ) Prev2;

OR

WITH CTE
     AS (SELECT ID,
                Number,
                ROW_NUMBER() OVER (ORDER BY ID) AS rn
         FROM   Mytable)
SELECT ID,
       This.Number AS CurrentValue,
       Prev.Number AS PreviousValue
FROM   CTE This
       LEFT JOIN CTE Prev
         ON Prev.rn + 1 = This.rn; 

对于SQL Server 2012

And for SQL Server 2012

SELECT
   ID,
   Number AS CurrentValue,
   LAG(Number) OVER (ORDER BY ID) AS PreviousValue
FROM
   MyTable

这篇关于如何使用递归CTE获取上一行和当前行的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！