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

来源：互联网发布：linux dns 添加mx记录编辑：程序博客网时间：2024/05/16 10:41

假设表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