sql server UPDATE 多表关联 使用

a_test 表 列a主键

a  b          c            e
1 11         3          0

2 12         3          0

3 13         3          0

 

期待的结果

a  b          c            e
1 11         3          14
2 12         3          29
3 13         3          45

 

也就是列e每一行的值都要是 上一行的e +本行b+本行c

 

SELECT的写法：

select a, b,c,
(select sum(convert(int,b)+convert(int,c)) from a_test b where b.a <= a.a) as e
from a_test a

 

UPDATE的写法：

UPDATE a_test
SET e = t2.e
FROM a_test as t1
INNER JOIN (select a,
(select sum(convert(int,b)+convert(int,c)) from a_test b where b.a <= a.a) as e
from a_test a) as t2
ON (t1.a = t2.a)