SQL Server 中根据上条记录的字段数值批量更新同一字段

Question

在Sql Server中，我有一个简单的表存储金额和余额如下所示：

ID | 日期| 发生金额| 余额
-------------------------------------
101 | 1/15/2017 | 3.00 | 67.50
102 | 1/16/2017 | 5.00 | 72.50
103 | 1/19/2017 | 9.00 | 81.50
104 | 1/20/2017 | -2.00 | 79.50

如果我更改了一个发生金额，我需要更新该记录之后的所有余额。

ID | 日期| 发生金额| 余额
-------------------------------------
101 | 1/15/2017 | 3.00 | 67.50
102 | 1/16/2017 | * 5.02 * | * 72.52 *
103 | 1/19/2017 | 9.00 | * 81.52 *
104 | 1/20/2017 | -2.00 | * 79.52 *

到目前为止，我在表中有超过1亿条记录。 要做这个工作，我不想使用sql游标或客户端程序，它将提交大量的更新语句，需要几个小时才能完成。 是否可以在一个sql语句中重新计算整个表的余额？

Answer 1

如果你是针对一个现有值的变更，那么这个处理其实很简单：

update tb set 余额 = 余额 + (更新记录的新值 - 更新记录的旧值) where id >= 更新记录的 id

Answer 2

CREATE TABLE tb(
	ID int PRIMARY KEY,
	日期 datetime,
	发生金额 decimal(18, 2),
	余额  decimal(18, 2)
);
INSERT tb VALUES
(101, '1/15/2017', 3.00, 67.50),
(102, '1/16/2017', 5.00, 72.50),
(103, '1/19/2017', 9.00, 81.50),
(104, '1/20/2017', -2.00, 79.50);
GO
-- 通过触发器实现自动更新余额（需要 >= SQL 2012 的版本）
CREATE TRIGGER TR_TB_UPDATE
	ON tb FOR UPDATE
AS
SET NOCOUNT ON;
UPDATE DATA SET 余额 = DATA.余额 + U.diff
FROM tb DATA
	INNER JOIN(
		SELECT
			i.id,
			diff = SUM(I.发生金额 - D.发生金额) OVER(ORDER BY I.id),
			next_id = MIN(i.id) OVER(ORDER BY i.id ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
		FROM inserted I, deleted D
		WHERE I.id = D.id
	) U ON DATA.id >= U.id AND( DATA.id < U.next_id OR U.next_id IS NULL)
;
GO

SELECT * FROM tb;

UPDATE tb SET 发生金额 = 5.02 WHERE id = 102;
SELECT * FROM tb;


UPDATE tb SET 发生金额 = 5.02 WHERE id IN( 101, 103)
SELECT * FROM tb;
GO

DROP TABLE tb;