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

  • 问题

  • 在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语句中重新计算整个表的余额?

    2017年4月19日 15:02

全部回复

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

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

    2017年4月20日 0:45
  • 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;
    

    2017年4月20日 2:04