sql-server - SQL 重新排序记录

Question

我有一个项目列表，需要无间隙地重新排序。它开始于:

ID   Sequence
123    1
125    2
155    3
158    4
165    6
170    9

我需要结束(请注意，序列 6 更改为 5，序列 9 更改为 6)

ID   Sequence
123    1
125    2
155    3
158    4
165    5
170    6

我尝试过使用此更新语句

UPDATE  tblA
SET     tblA.Sequence = temp.Sequence
FROM    ( SELECT    id ,
                    ROW_NUMBER() OVER ( ORDER BY Sequence, ID ) AS Sequence
          FROM      dbo.tblA
        ) AS temp

但我最终只是......

ID   Sequence
123    1
125    1
155    1
158    6
165    6
170    6

从更新中拉出 select 语句会产生正确的结果。将其更改为类似

 UPDATE tblA
 SET    tblA.Sequence = temp.NewSequence
 FROM   ( SELECT    id ,
                    ROW_NUMBER() OVER ( PARTITION BY id ORDER BY Sequence, id ) 
                                                               AS NewSequence
          FROM      dbo.tblA
        ) AS temp

产生结果

ID   Sequence
123    1
125    1
155    1
158    1
165    1
170    1

我做错了什么？

Answer 1

您需要将重新编号的集合与原始集合中的 ID 相关联，否则您将使用临时集合中其他某个 ID 的序列号来更新原始 ID:

UPDATE a
SET a.Sequence = temp.Sequence
FROM    
    tblA a JOIN
    (
        SELECT id, ROW_NUMBER() OVER (ORDER BY Sequence, ID) AS Sequence
        FROM dbo.tblA
    ) AS temp ON temp.ID = a.ID