如何在SQL Server中删除一条记录后重新排序记录(SortOrder列)?

I was wondering what the most efficient way is to reorder my records after I delete a record. Basically I have something like (ItemID, SortOrder). So when I delete an item, the SortOrder column will no longer be incremental. I want to re number sort order so that the numbers are incremental again.

我想知道，在我删除一条记录后，最有效的方法是重新排序我的记录。基本上我有一些东西(ItemID, SortOrder)。因此，当我删除一个条目时，SortOrder列将不再是递增的。我想要重新排序，使数字再次递增。

I was thinking maybe I could do this with the RANK feature...but I'm not quite sure how to do it.

我在想也许我可以用等级特征来做这个……但我不太确定该怎么做。

2 个解决方案

#1

If you're using something that supports T-SQL (i.e. not Compact Edition), then this should work.

如果您使用的是支持T-SQL(即不支持精简版)的东西，那么它应该可以工作。

Assuming your table is named Item and the orders are currently incremental (no gaps),

假设您的表被命名为Item，并且订单当前是递增的(没有间隙)，

declare @SortOrder int

select @SortOrder = SortOrder from Item where ItemID = @ItemID

update Item set SortOrder = SortOrder - 1 where SortOrder > @SortOrder

delete Item where ItemID = @ItemID

#2

Assuming this is a periodic tidy up then the following would work to "fix" all gaps.

假设这是一个周期性的清理，那么下面的内容将会“修复”所有的间隙。

Some test data

SELECT *
INTO   #TEST
FROM   (SELECT 1, 1, 1
        UNION ALL 
        SELECT 2, 1, 3
        UNION ALL 
        SELECT 3, 1, 4
        UNION ALL
        SELECT 4, 2, 2
        UNION ALL 
        SELECT 5, 2, 3
        UNION ALL 
        SELECT 6, 2, 4)H(PK, ItemID, SortOrder)

The code

WITH Tidied
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY ItemID 
                                       ORDER BY SortOrder) AS NewSortOrder
         FROM   #TEST)
UPDATE Tidied
SET    SortOrder = NewSortOrder