用上一行更新记录 [英] Update record with previous row

问题描述

我遇到一种情况，我需要使用上一行的值来更新记录.

I have a situation where I need to update the records with previous row value.

来源:

|MatId | BaseId |Flag|Pkg1| CS1
--------------------------------
|3001  | 3001   | 1  | 20 | 2 |
|3002  | 3001   | 0  | 15 | 3 |
|3003  | 3001   | 0  | 10 | 4 |

此处3001(MatID)和3001(BaseID)相同，因此FLAG = 1，在下一个记录中，只有BASEID相同.输出应为仅PKG1 字段，并使用当前行值进行更新.

Here both 3001 (MatID) and 3001(BaseID) are same so FLAG =1, in the next record only BASEID is same. The output should be only PKG1 field updated with the current row value.

目标或输出:

|MatId | BaseId|Flag|Pkg1|CS1
------------------------------
|3001  | 3001  | 1  | 20 | 2|
|3002  | 3001  | 0  | 20 | 3|
|3003  | 3001  | 0  | 20 | 4|

从上面的目标中可以看出，我必须使用第一个记录20中的值来更新PKG1中的两个值.另外，Pkg1中有许多列，如何用一个查询来更新所有列?

As seen in the target above i have to update the two values in PKG1 with the value from first record 20. Also there are many columns with Pkg1, how to update all the columns with a single query?

非常感谢您的帮助.
谢谢.

Any help is very much appreciated.
Thanks.

推荐答案

借助

To get Previous and Next value with the help of LEAD and LAG Function in SQL Server is very simple. If you are using an earlier version of SQL Server than 2012 which does not support LEAD and LAG function we can use ROW_NUMBER().

尝试使用类似这样的内容:

Try to use something like this:

;WITH t AS
(
    select LAG(MatId) OVER (ORDER BY MatId) AS previousMatId
    ,      BaseId
    ,      MatId
    from   TABLE
)
update tab
set    tab.Pkg1 = p.Pkg1
from   TABLE tab
       inner join t on tab.MatId = t.MatId and t.BaseId = t.previousMatId
       left join (select MatId AS MatId 
                  ,     ISNULL(LAG(Pkg1) OVER (ORDER BY MatId), Pkg1) AS Pkg1
                  from TABLE) p on t.MatId = p.MatId

这篇关于用上一行更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！