用的游标,写的一般
程序代码:
DECLARE @t TABLE( id INT, 时间 VARCHAR(20), kwmc VARCHAR(4), 数值 FLOAT, 合值 FLOAT )
INSERT @t( id, 时间, kwmc, 数值, 合值 )
VALUES
( 0, NULL, '起初', 19.0, NULL ),
( 1, '2020-09-14', '', -1, NULL ),
( 2, '2020-09-14', '', 1, NULL ),
( 3, '2020-09-14', '', 1, NULL ),
( 4, '2020-09-15', '', -1, NULL ),
( 5, '2020-09-19', '', -1, NULL ),
( 6, '2020-09-23', '', -1, NULL ),
( 7, '2020-09-25', '', -1, NULL ),
( 8, '2020-10-12', '', -2, NULL ),
( 9, '2020-10-12', '', -5, NULL ),
( 10, '2020-10-12', '', -1, NULL )
SELECT * FROM @t AS T ORDER BY T.id
--关提示
SET NOCOUNT ON
--声明游标
DECLARE 游标1 CURSOR SCROLL FOR SELECT T.id FROM @t AS T ORDER BY T.id
--打开游标
OPEN 游标1
DECLARE @上一个id INT, @当前id INT
--第一条
FETCH FIRST FROM 游标1
INTO @上一个id
--第二条数据
FETCH NEXT FROM 游标1
INTO @当前id
--改最开始的
UPDATE T SET T.合值 = ( SELECT T2.数值 FROM @t AS T2 WHERE id = @上一个id ) + 数值 FROM @t AS T WHERE id = @当前id
SELECT @上一个id = @当前id
FETCH NEXT FROM 游标1
INTO @当前id
WHILE @@fetch_status = 0
BEGIN
UPDATE T
SET T.合值 = ( SELECT T2.合值 FROM @t AS T2 WHERE T2.id = @上一个id ) + 数值
FROM @t AS T
WHERE id = @当前id
SELECT @上一个id = @当前id
FETCH NEXT FROM 游标1
INTO @当前id
END
--关闭游标
CLOSE 游标1
--释放游标
DEALLOCATE 游标1
SELECT * FROM @t AS T