整理最近新学到的几种sql写法（一）动态“循环”更新表内数据

 

动态“循环”更新表内数据，即此行某列的值与上一行的结果有关

 1 
 2 CREATE TABLE t1
 3 (
 4     id    INT IDENTITY,
 5     a    INT,
 6     b    INT,
 7     c    INT,
 8     d    INT,
 9     e    INT
10 )
11 
12 INSERT INTO [t1] (
13     [a],
14     [b],
15     [c],
16     [d],
17     [e]
18 ) 
19 SELECT    1, 2, 3, 4, 0
20 UNION
21 SELECT    2, 3, 4, 5, 0
22 UNION
23 SELECT    3, 4, 5, 6, 0
24 UNION
25 SELECT    4, 5, 6, 7, 0
26 
27 
28 
29 SELECT    *
30 FROM    [t1]
31 
32 DECLARE @e INT
33 UPDATE [t1]
34 SET @e = ((ISNULL(@e, 0)+[c])*[d])/(a+[b]), e=@e
35 
36