光标以使用来自上一行和当前行的值更新行 [英] cursor to update a row with values from the previous and current rows

问题描述

同伴查询作者，

我有一个表格如下：

myTable t1
col2 col3
 2    1
 3    0
 4    0
 5    0
 6    0

，并且我想更新col3上的每个零，使用上一行中col3的值加上当前行中col2的值。所以我的表会像下面这样：

and I want to update each zero on col3 with the value of col3 in the previous row plus the value of col2 in the current row. So my table would de like the following:

myTable t1
col2 col3
 2    1 
 3    4  (1+3)
 4    8  (4+4)
 5    13 (5+8) 
 6    19 (6+13)

我错过了这里的逻辑，也许短视。我使用游标如下：

I'm missing the logic here, short-sightedness perhaps. I was trying it with a cursor as follows:

DECLARE @var3 FLOAT

DECLARE cursor3 CURSOR FOR
SELECT col2, col3 FROM table1
FOR UPDATE OF col3
OPEN cursor3


FETCH FIRST FROM cursor3
WHILE (@@FETCH_STATUS > -1)
BEGIN
 UPDATE @table1
 SET col3 = isnull(@var3, 0) + isnull(col2, 0)
 WHERE CURRENT OF cursor3
 FETCH NEXT FROM cursor3 INTO @var3
END

但是错了。
任何想法？

but it's wrong. Any ideas?

提前感谢。

推荐答案

OK，Try this。

OK, Try this.

CREATE TABLE MyTable (Id INT Identity, Col2 int, Col3 int)

INSERT INTO MyTable (Col2, Col3)
VALUES (2,1), (3,0), (4,0),(5,0),(6,0)

SELECT * from MyTable

WHILE (SELECT COUNT(*) FROM MyTable WHERE Col3=0) > 0
BEGIN
    UPDATE TOP (1) MyTable
    SET CoL3 = (Mytable.col2 + (select col3 from mytable t2 where (t2.id = mytable.id-1)))
    WHERE Col3 = 0
END

SELECT * from MyTable

使用 WHILE 循环，在大多数情况下应该比光标快。

Uses a WHILE loop which should be faster than a cursor under most circumstances.

这篇关于光标以使用来自上一行和当前行的值更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！