SQL SQL增加一个数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8401552/
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
SQL increment a number
提问by Ish
Problem:
问题：
I want to increment a number based on a table. So for example, if a table contains row
我想根据表格增加一个数字。例如，如果一个表包含行
1 1 2 3 4 4 4 5
mytable column should increment based on this taking the max(row) + 1 in the above column. So the outcome should look like this:
mytable 列应该基于此增加上列中的 max(row) + 1。所以结果应该是这样的：
6 6 7 8 9 9 9 10
This is the code so far:
这是到目前为止的代码：
OPEN cur
DECLARE @WORKING_ON_ID INT
FETCH NEXT FROM cur INTO @WORKING_ON_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MAX_ID = @MAX_ID + 1
UPDATE
#WorkingTable
SET
ID = @MAX_ID
WHERE
ID = @WORKING_ON_ID
FETCH NEXT FROM cur INTO @WORKING_ON_ID
END
CLOSE cur
DEALLOCATE cur
Could you please help me in getting a solution to this problem. Thanks!
你能帮我解决这个问题吗？谢谢！
回答by Marco
I think you could do it easily with this:
我认为你可以很容易地做到这一点：
UPDATE your_table
SET id = id + (SELECT MAX(id) FROM your_table)
回答by Matten
Wouldn't it be easier to just take the maximum and add it to this ID column? (Remember: the ID column can't be an identity column, otherwise an update will fail)
取最大值并将其添加到此 ID 列中不是更容易吗？（记住：ID 列不能是标识列，否则更新会失败）
DECLARE @MAXID INT
SELECT @MAXID = MAX(ID) FROM #WorkingTable
UPDATE #WorkingTable SET ID = ID + @MAXID
回答by Ali Rasoulian
Please Try this Code:
请试试这个代码：
Declare @count int = 0
UPDATE table
SET @count = code = @count + 1
回答by Pieter
Why use a cursor? Wouldn't this solve your problem as well:
为什么要使用游标？这是否也能解决您的问题：
DECLARE @MAXID int
SELECT @MAXID=MAX(ID) FROM YourTable
UPDATE YourTable SET ID = ID + @MAXID
回答by Andriy M
In SQL Server 2005 or later version:
在 SQL Server 2005 或更高版本中：
WITH cte AS (
SELECT ID, MAX(ID) OVER () AS delta FROM atable
)
UPDATE cte
SET ID = ID + delta;
