更新查询-前一行x和当前行y的和

Question

我有一个表LedgerData，需要更新Balance.

表结构

CREATE TABLE [dbo].[LedgerData]
(
[Id] INT NOT NULL,
[CustomerId] INT NOT NULL,
[Credit] INT,
[Debit] INT,
[Balance] INT
CONSTRAINT [PK_dbo_LedgerData] 
PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

示例数据

INSERT INTO [dbo].[LedgerData] VALUES (1, 1, 50, 0, 0);
INSERT INTO [dbo].[LedgerData] VALUES (2, 1, 0, 25, 0);
INSERT INTO [dbo].[LedgerData] VALUES (3, 2, 0, 75, 0);
INSERT INTO [dbo].[LedgerData] VALUES (4, 1, 0, 10, 0);
INSERT INTO [dbo].[LedgerData] VALUES (5, 2, 5, 0, 0);
INSERT INTO [dbo].[LedgerData] VALUES (6, 1, 10, 25, 0);

我试图更新余额列客户明智的ORDER BY [Id] ASC，但它没有像预期的那样更新。我还研究了sql查询来计算和添加前几行的总和

请帮我计算余额栏Balance = (Previous Row Balance + Credit - Debit)

Answer 1

理想情况下，这是你应该做的事情作为你INSERT数据，通过获得以前的值(并锁定表，使其他INSERT语句不能发生，以避免竞争)，然后为Balance提供一个值。但是，您可以使用累积SUM和可更新CTE来UPDATE所有行:

WITH CTE AS(
SELECT ID,
CustomerID,
Balance,
0 + SUM(Credit) OVER (PARTITION BY CustomerID ORDER BY ID) - SUM(Debit) OVER (PARTITION BY CustomerID ORDER BY ID) AS NewBalance
FROM dbo.LedgerData)
UPDATE CTE
SET Balance = NewBalance;
GO
SELECT *
FROM dbo.LedgerData;

或者，根本不存储聚合值，并使用VIEW，以便始终可以使用我在CTE中使用的相同表达式(准确地)计算值。例如:

CREATE VIEW dbo.LedgerDataCumulative
AS
SELECT Id,
CustomerId,
Credit,
Debit,
SUM(Credit) OVER (PARTITION BY CustomerID ORDER BY ID) - SUM(Debit) OVER (PARTITION BY CustomerID ORDER BY ID) AS Balance
FROM dbo.LedgerData;
GO

Answer 2

可以通过单个窗口函数执行更新。

with upd_cte as (
select *, sum([Credit]-[Debit]) over (partition by customerId order by id) sum_over
from #LedgerData)
update upd_cte
set Balance=sum_over;