如何更新当前记录的某个字段等于本记录的某个字段+上一条记录的某字段之和 .

最新推荐文章于 2023-02-01 14:06:03 发布

a1586141909

最新推荐文章于 2023-02-01 14:06:03 发布

阅读量316

点赞数

本文链接：https://blog.csdn.net/a1586141909/article/details/8695052

版权

假设表tabFomula字段如下

BuyDate InOut Balance

其中BuyDate为主键，需要更新Balance=本记录的InOut+上一条记录的Balance

更新语句可以这样写

UPDATE A SET Balance = A.InOut + ISNULL((SELECT TOP 1 Balance FROM tabFomula B WHERE B.BuyDate<A.BuyDate ORDER BY B.BuyDate DESC ), 0)
FROM tabFomula A

------------------------------------------------------------------------

BuyDate InOut Balance

2010-07-01 00:00:00.000 100.00 100.00
2010-07-02 00:00:00.000 100.00 200.00
2010-07-03 00:00:00.000 100.00 300.00
2010-07-04 00:00:00.000 100.00 400.00
2010-07-05 00:00:00.000 -100.00 300.00
2010-07-05 10:00:00.000 -100.00 200.00
2010-07-06 00:00:00.000 -100.00 200.00
2010-07-07 00:00:00.000 100.00 300.00
2010-07-08 00:00:00.000 100.00 400.00
2010-07-09 00:00:00.000 100.00 500.00
2010-07-10 00:00:00.000 100.00 600.00
2010-07-11 00:00:00.000 100.00 700.00