时间戳

首页 > 解决方案 > 删除时手动排序顺序问题

sql - 删除时手动排序顺序问题

问题描述

我有这个 SQL

;WITH Sorted AS (
    SELECT  authorid,
            ROW_NUMBER() OVER (ORDER BY sorted) AS RowNumber
    FROM    author
    WHERE   authorid <> @id
)

UPDATE  p
SET     p.sorted = 
        (CASE 
            WHEN p.authorid =  @id THEN @id
            WHEN s.RowNumber >= @id THEN s.RowNumber + 1
            ELSE s.RowNumber
        END)
FROM    author p
        LEFT JOIN Sorted s ON p.authorid = s.authorid

但是这段代码是有问题的，因为我将值加了 1，如果序列的顺序是 1、2、3、4、5，它就可以工作。

但是如果删除一条记录，它会搞砸一切

所以克服这个问题，我在想如果我可以使用primarykey列作为排序列中的相同值，即使我删除，排序应该没有任何问题，上面的代码将正确地对记录进行排序。

因为在某一时刻，只有 1 个项目会向上或向下排序。

它更像是行将被交换，

知道我怎样才能让它工作吗？

这是示例数据

标签： sqlsql-serversortingsql-update

解决方案

我无法理解您为什么要在表格中保留一个单独的连续数字序列。author每次从表中选择时，维护此序列可能比即时计算排序序列要慢得多。

无论如何，假设您有充分的理由，这里是代码：

;WITH Sorted AS (
    SELECT  authorid,
            ROW_NUMBER() OVER ( ORDER BY authorid ) AS RowNumber
    FROM    author
    -- If you want to exclude a specific author from sorting
    -- This will also not update sorted value for this author
    -- WHERE authorid <> @id
)
UPDATE a
SET sorted = RowNumber
FROM Sorted AS a

注释：

如果您这样做是为了分页，那么这是一种典型的方法。

RE：“因为在某一时刻，只有 1 个项目会向上或向下排序。” - 数据库表不是链接列表，您可以按照自己喜欢的顺序移动（重新链接）项目。磁盘上数据库表数据的顺序由可用空间可用性和数据大小决定（类似于操作系统中文件空间的分配方式），sorted除非有聚集索引，否则不会按列顺序排列。如果您有一个聚集索引，那么 SQL Server 将“尝试维护”该顺序，但它不会重新排序磁盘上已经存在的数据，除非您明确告诉它 ( ALTER INDEX ... ON ... REBUILD)。

推荐阅读