具有累积值的 SQL 更新表

Question

我有这张表:

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 值的原因是因为我可以从另一个存储任何股票代码的当前手头数量的表中获取该值。

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

我想更新上表，以便 OnHand 列根据先前记录的库存和移动显示该行日期的 QtyOnHand，这样在更新结束时看起来像这样:

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 实现此目的。但是性能真的很糟糕，超过了数千条记录。

我可以运行一些基于 SET 的 UPDATE 语句来获得相同的结果吗？

Answer 1

试试这个 ( 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，您可以修改上面的查询，如下所示 ( 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