将光标移到一行，使用前一行和当前行的值更新该行

Question

各位查询编写者，

我有一个如下的表：

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

我希望将col3中的每个零更新为前一行的col3值加上当前行的col2值。因此，我的表格应该如下所示：

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

我在这里缺少逻辑，可能是目光短浅。我尝试使用以下代码进行光标操作：

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

但是这是错误的。有什么想法吗？谢谢提前。

- Lynx Kepler

---

你的表格上还有其他列，比如ID列吗？游标几乎从来不是一个好主意... - JNK

还有其他列，但没有主键。这是一个用于生成报告的临时表。我相信游标存在是为了解决这样的问题。 - Lynx Kepler

游标存在于这样的问题中，但这并不意味着它们是最好/最合适的解决方案。如果您向临时表添加一个标识列，这将变得容易多了，大约快10000倍。 - JNK

Answer 1

好的，试试这个。

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 循环通常比游标更快。

- JNK

---

那么它做你想要的事情了吗？实际数据集有多大，速度是否足够快？当然，你也可以将其更改为使用#Temp或@TableVar类型的表，我只是为了测试而使用了真实的表。 - JNK

数据集大约有100行，任何解决方案都可以接受。总时间不超过2秒，全部包括在内。我使用@tableVar。感谢您的专注时间！ - Lynx Kepler

@JNK 除了使用 While 循环之外，还有其他的方法吗？因为在我的情况下，表中的记录可能会达到 200,000+，使用 while 循环肯定会减慢速度。 - Hemant Sisodia

@HemantSisodia，你可以将WHILE中的条件替换为EXISTS，这样会更快，因为它会短路。记录的数量并不太重要。如果你需要真正快速，你可以使用主键来循环处理这些记录，即仅处理1-10,000之间的主键值，然后再处理下一个10k等等。 - JNK

@JNK 感谢您的回复，我理解了您的第一部分答案。您能否通过任何 Fiddle 解释第二部分，即如何分段循环 PK 值？那将非常有帮助。 - Hemant Sisodia

Answer 2

我给表格添加了一个标识列，并最终使用了类似这样的代码：

DECLARE @saldo_Q_previous FLOAT
DECLARE @ID INTEGER

DECLARE cursor3 CURSOR FOR
SELECT ID FROM @myTable
FOR UPDATE OF col2
OPEN cursor3

FETCH NEXT FROM cursor3 INTO @ID
FETCH NEXT FROM cursor3 INTO @ID

WHILE (@@FETCH_STATUS > -1)
BEGIN

    SET @col2_previous = ISNULL((SELECT TOP 1 col2 FROM @myTable WHERE ID < @ID ORDER BY ID DESC), 0)
    SET @vrQ = ISNULL((SELECT TOP 1 vr_Q FROM @myTable WHERE ID < @ID ORDER BY ID DESC), 0)

    UPDATE @myTable
    SET col2 = isnull(@col2_previous, 0) + isnull(vrMov_Q, 0)
    WHERE CURRENT OF cursor3

    FETCH NEXT FROM cursor3 INTO @ID
END

CLOSE cursor3
DEALLOCATE cursor3

它解决了我的问题。谢谢大家。

- Lynx Kepler

Answer 3

这是一个使用公共表达式（CTE）更新数据的单个UPDATE语句。

WITH myTable2 AS
    (
    SELECT col2, col3, ROW_NUMBER() OVER (ORDER BY col2) AS sequence
    FROM myTable
    ),
  newTable AS
    (
    SELECT t1.col2, SUM(t2.col2) - SUM(t2.col3) AS col3
    FROM myTable2 t1
    LEFT OUTER JOIN myTable2 t2 ON t1.sequence >= t2.sequence
    GROUP BY t1.col2
    )

UPDATE myTable
SET col3 = newTable.col3
FROM myTable
JOIN newTable on myTable.col2 = newTable.col2
;

- bobs

---

这是递归的吗？即它会使用更新后的列3值来处理下一行吗？ - JNK

@JNK - 这不是递归的。请注意，在newTable CTE中，连接条件使用>=，以便可以对col2的所有先前副本进行求和。 col3的总和是减去第一行中的1的简单方法。 - bobs

Answer 4

就编程而言，使用游标的主要强制性原因是如果不这样做，将会对您的关系数据库管理系统造成太大的影响。几乎总是可以使用 WHILE 循环代替 CURSOR；一次处理一个记录；当由于任何原因您需要迭代大量记录时，这非常有用...CURSOR 操作比等效的 SET 操作更高效。

因此，总体而言，它归结为速度和开销与效率之间的平衡...

游标基本上是最慢的方法，但具有最小的开销，并且即使在 MSSQL 2012 中仍然有用...

- OldSqlDude