SQL运行总计重置

Question

我有一张桌子，上面有日期和交易记录。我需要计算运行总计（用总和划分很容易），除了当“金额”中存在负值时需要将其重置为零，然后再次开始计算运行总计。有任何想法吗？

account|amount|     date          |total|
123456 |50    |2017-01-01 00:00:00|50
123456 |50    |2017-01-02 00:00:00|100
123456 |100   |2017-01-03 00:00:00|200
123456 |-50   |2017-01-04 00:00:00|0
123456 |100   |2017-01-05 00:00:00|100
123456 |100   |2017-01-06 00:00:00|200

Answer 1

使用更新变量方法，您可以使用重置选项计算运行总计。

declare @tblItems table(
 ID int identity(1, 1),
 Amount decimal(12, 3),
 RunningTotal decimal(12, 3) default(0)
)

insert into @tblItems(Amount)
values
 (50),
 (20),
 (-100),
 (5),
 (10)
;

DECLARE @RunningTotal decimal(12, 3) = 0;

UPDATE @tblItems
SET @RunningTotal = RunningTotal = case when amount<0 then 0 else (@RunningTotal + Amount) end
FROM @tblItems;

SELECT ID, Amount, RunningTotal
FROM @tblItems
ORDER BY ID;