問題描述

我有這張桌子:

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

這篇關于具有累積值的 SQL 更新表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！