SQL 游标使用前一行和当前行的值更新一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4089777/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
cursor to update a row with values from the previous and current rows
提问by Lynx Kepler
Fellow Query Writers,
各位查询作者，
I have a table as follows:
我有一个表如下：
myTable t1
col2 col3
2 1
3 0
4 0
5 0
6 0
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:
我想用前一行中 col3 的值加上当前行中 col2 的值更新 col3 上的每个零。所以我的表会喜欢以下内容：
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?
但这是错误的。有任何想法吗？
Thanks in advance.
提前致谢。
回答by JNK
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
Uses a WHILEloop which should be faster than a cursor under most circumstances.
使用WHILE在大多数情况下应该比游标更快的循环。
回答by Lynx Kepler
I added an identity column to my table and ended up using a code like this:
我在我的表中添加了一个标识列，并最终使用了这样的代码：
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
It solved my problem. Thank you all.
它解决了我的问题。谢谢你们。
回答by OldSqlDude
FWIW The major, compelling reason to use a CURSOR is when not doing so, will put too much of a hit on your rdbms. You can almost always use a WHILE loop instead of a CURSOR; processing ONE record at a time; can be quite useful when for whatever reason you may need to iterate a large number of records... CURSOR operations are exponentially more efficient than the equivalent SET operation.
FWIW 使用 CURSOR 的主要、令人信服的原因是，如果不这样做，会对您的 rdbms 造成太大影响。您几乎总是可以使用 WHILE 循环而不是 CURSOR；一次处理一个记录；无论出于何种原因，您可能需要迭代大量记录时，都非常有用...... CURSOR 操作比等效的 SET 操作效率高出指数级。
So in general it comes down to speed & overhead vs. efficiency...
所以总的来说，它归结为速度和开销与效率......
CURSORS are pretty much the slowest way to go, but have the least amount of overhead and are still useful even in MSSQL 2012 ...
游标几乎是最慢的方法，但开销最少，即使在 MSSQL 2012 中仍然有用......
回答by bobs
Here is a single UPDATE statement that uses common table expressions (CTE) to update the data.
这是一个使用公用表表达式 (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
;
