SQL表按Preference字段排序 删除记录后断档序号重排咨询

核心实现逻辑：基于窗口函数按现有优先级顺序重生成连续序号，无需针对单条删除做定向偏移更新，可覆盖单条删除、批量删除、序号乱序等所有断档场景。

删除后即时重排实现 #

将重排逻辑与自动删除逻辑放在同一事务中执行，删除完成后立刻修正序号，从根源避免断档产生。

BEGIN TRANSACTION;

-- 原有自动删除记录逻辑，示例如下
-- DELETE FROM [RadioQDB].[dbo].[Rad5] WHERE 自动删除匹配条件;

-- 按现有优先级顺序重生成连续1开头的序号
WITH ResortCTE AS (
    SELECT 
        Id,
        ROW_NUMBER() OVER (ORDER BY Preference ASC, Id ASC) AS NewPreference
    FROM [RadioQDB].[dbo].[Rad5]
    WHERE Preference <> 0 -- 跳过未分配初始优先级的新记录
)
UPDATE ResortCTE
SET Preference = NewPreference;

COMMIT TRANSACTION;

该写法特性：

• 兼容任意断档场景：无论删除多少条记录、原有序号乱序程度如何，重排后均会生成从1开始的连续序号，比如原序号1、3、4、5会被修正为1、2、3、4
• 排序稳定性：增加Id作为兜底排序字段，避免多条记录Preference值重复时排序结果随机，打乱用户已配置的拖拽顺序
• 逻辑隔离：自动跳过Preference=0的未初始化新记录，不会和原有新记录分配优先级的逻辑冲突

兼容新记录分配的全量维护脚本 #

如果不想将重排逻辑绑定在删除操作上，可直接替换原有新记录分配Preference的脚本，每次执行时先修正已有记录的断档，再为新记录分配顺延的连续序号：

WITH 
-- 重排已初始化记录，修正断档
ResortExistCTE AS (
    SELECT 
        Id,
        ROW_NUMBER() OVER (ORDER BY Preference ASC, Id ASC) AS NewPref
    FROM [RadioQDB].[dbo].[Rad5]
    WHERE Preference <> 0
),
-- 为未初始化新记录生成顺延序号
AssignNewCTE AS (
    SELECT 
        Id,
        ISNULL((SELECT MAX(NewPref) FROM ResortExistCTE),0) + ROW_NUMBER() OVER (ORDER BY Id) AS NewPref
    FROM [RadioQDB].[dbo].[Rad5]
    WHERE Preference = 0
)
-- 统一更新所有记录的Preference值
UPDATE t
SET t.Preference = c.NewPref
FROM [RadioQDB].[dbo].[Rad5] t
INNER JOIN (
    SELECT Id, NewPref FROM ResortExistCTE
    UNION ALL
    SELECT Id, NewPref FROM AssignNewCTE
) c ON t.Id = c.Id;

注意事项 #

• 所有涉及记录删除、优先级更新、新记录分配的操作建议放在同一事务中执行，避免并发请求导致序号重复、顺序错乱
• 不建议使用「定位被删除的Preference值，将所有大于该值的记录序号减1」的实现，该写法在批量删除、并发插入场景下极易出现更新遗漏、序号重复问题
• 重排时的排序规则必须优先按原Preference字段升序排列，禁止直接以Id、创建时间等字段排序，否则会覆盖用户拖拽调整后的自定义顺序

内容的提问来源于stack exchange，提问作者Monks Den