重置或更新数据库表中的行位置整数

I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this "Order Rank Integer".

我正在开发SQL Server 2008中的一个存储过程，用于重置数据库表中的一个整数列。此整数列存储或持久存储项行的显示顺序。用户可以按特定的排序顺序拖放项目，我们使用“order Rank Integer”将该顺序保存在数据库表中。

Display queries for items always append a "ORDER BY OrderRankInt" when retrieving data so the user sees the items in the order they previously specified.

在检索数据时，对项的显示查询总是附加一个“OrderRankInt下的订单”，以便用户按照之前指定的顺序查看项。

The problem is that this integer column collects a lot of duplicate values after the table items are re-ordered a bit. Hence...

问题是，这个整数列在表项被重新排序后收集了很多重复的值。因此……

Table
--------
Name | OrderRankInt
a    | 1
b    | 2
c    | 3
d    | 4
e    | 5
f    | 6

After a lot of reordering by the user becomes....

大量的重新排序后由用户成为....

Table
--------
Name | OrderRankInt
a    | 1
b    | 2
c    | 2
d    | 2
e    | 2
f    | 6

These duplicates are primarily because of insertions and user specified order numbers. We're not trying to prevent duplicate order ranks, but we'd like a way to 'Fix' the table on item inserts/modifies.

这些重复主要是因为插入和用户指定的订单号。我们并不是要阻止重复的顺序排列，但是我们想要一种方法来“修复”表上的项目插入/修改。

Is there a way I can reset the OrderRankInt column with a single UPDATE Query? Or do I need to use a cursor? What would the syntax for that cursor look like?

是否有一种方法可以用一个更新查询重置OrderRankInt列?还是需要使用光标?这个游标的语法是什么样子的?

Thanks, Kervin

谢谢,Kervin

EDIT

编辑

Update with Remus Rusanu solution. Thanks!!

使用Remus Rusanu解决方案进行更新。谢谢! !

CREATE PROCEDURE EPC_FixTableOrder
@sectionId int = 0
AS
BEGIN

-- "Common Table Expression" to append a 'Row Number' to the table
WITH tempTable AS 
(
    SELECT OrderRankInt, ROW_NUMBER() OVER (ORDER BY OrderRankInt) AS rn
    FROM dbo.[Table]
    WHERE sectionId = @sectionId -- Fix for a specified section
)
UPDATE tempTable        
SET OrderRankInt = rn;  -- Set the Order number to the row number via CTE

END
GO

2 个解决方案

#1

with cte as (
 select OrderId, row_number() over (order by Name) as rn
 from Table)
update cte
 set OrderId = rn;

This doesn't account for any foreign key relationships, I hope you are taken care of those.

这并不包括任何外国的关键关系，我希望你能注意到这些。

#2

Fake it. Make the column nullable, set the values to NULL, alter it to be an autonumber, and then turn off autonumber and nullable.

假的。使列为空，将值设置为NULL，将其更改为自动编号，然后关闭自动编号和可空。

(You could skip the nullable steps.)

(您可以跳过可空的步骤。)

#1

with cte as (
 select OrderId, row_number() over (order by Name) as rn
 from Table)
update cte
 set OrderId = rn;