具有累积值的 SQL 更新表

2021-09-10 00:00:00 sql tsql sql-server

我有这张桌子:

Date  |StockCode|DaysMovement|OnHand
29-Jul|SC123    |30          |500
28-Jul|SC123    |15          |NULL
27-Jul|SC123    |0           |NULL
26-Jul|SC123    |4           |NULL
25-Jul|SC123    |-2          |NULL
24-Jul|SC123    |0           |NULL

只有第一行有 OnHand 值的原因是因为我可以从另一个表中获取它，该表存储任何股票代码的当前手头数量.

The reason only the top row has an OnHand value is because I can get this from another table that stores the current qty on hand for any stock code.

表中的其他记录取自另一个表，该表记录了任何给定日期的所有移动.

The other records in the table are taken from another table that logs all the movement for any given day.

我想更新上表，以便 OnHand 列根据前一条记录的库存和变动显示该行日期的 QtyOnHand，更新结束时如下所示:

I want to update the above table so that the OnHand column shows the QtyOnHand for that row's date based on the previous record's stock and movement, such that is looks like this at the end of the update:

Date  |StockCode|DaysMovement|OnHand
29-Jul|SC123    |30          |500
28-Jul|SC123    |15          |470
27-Jul|SC123    |0           |455
26-Jul|SC123    |4           |455
25-Jul|SC123    |-2          |451
24-Jul|SC123    |0           |453

我目前正在使用 CURSOR 实现这一目标.但性能真的很糟糕，超过了数千条记录.

I'm currently achieving this with a CURSOR. But performance really sucks over thousands of records.

是否有一些基于 SET 的 UPDATE 语句可以运行以达到相同的结果?

Is there some SET-based UPDATE statement I can run that will achieve the same result?

推荐答案

试试这个 (小提琴演示)

Try this (Fiddle demo)

DECLARE @Movement INT , @OnHandRunning INT  

;WITH CTE AS
(
    SELECT TOP 100 percent DaysMovement, OnHand 
    FROM Table1
    ORDER BY [StockCode], [Date] DESC
)
UPDATE CTE SET @OnHandRunning = OnHand = COALESCE(@OnHandRunning - @Movement, OnHand),
               @Movement = DaysMovement

更新:对于多个StockCodes，您可以修改上面的查询，如下所示(小提琴演示 2):

UPDATE: For multiple StockCodes you can modify above query like below (Fiddle demo 2):

DECLARE @Movement INT , @OnHandRunning INT, @StockCode VARCHAR(10) = '' 

;WITH CTE AS
(
    SELECT TOP 100 percent DaysMovement, OnHand, StockCode  
    FROM Table1
    ORDER BY [StockCode],[Date] DESC
)
UPDATE CTE SET @OnHandRunning = OnHand = 
       CASE WHEN @StockCode<> StockCode THEN OnHand ELSE @OnHandRunning - @Movement END,
       @Movement = DaysMovement,
       @StockCode = StockCode

相关文章