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

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