SQL Server删除记录后按QuestionnaireID更新QuestionOrder为连续序列的方法

核心思路：利用SQL Server窗口函数ROW_NUMBER()，按QuestionnaireID分组后重新生成连续序号，覆盖原有QuestionOrder字段即可，同时保留题目原有先后顺序。

场景1：手动修复序号（删除记录后一次性执行） #

适合偶尔删除数据后临时修复的场景：

WITH ReorderCTE AS (
    SELECT 
        ID,
        QuestionnaireID,
        QuestionOrder,
        -- 按原有QuestionOrder排序保证题目顺序不变，生成连续新序号
        ROW_NUMBER() OVER (PARTITION BY QuestionnaireID ORDER BY QuestionOrder ASC) AS NewOrder
    FROM 你的表名 -- 替换为实际数据表名称
    -- 若只需要修复指定问卷，可取消注释下行并指定ID：
    -- WHERE QuestionnaireID = 1
)
UPDATE ReorderCTE 
SET QuestionOrder = NewOrder
WHERE QuestionOrder != NewOrder; -- 仅更新序号变化的行，降低性能开销

场景2：自动维护序号（删除记录后自动触发更新） #

适合需要长期自动维护序号连续性的场景，创建删除后触发器即可：

CREATE TRIGGER trg_AfterDeleteQuestion_ResetOrder
ON 你的表名 -- 替换为实际数据表名称
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅提取有记录被删除的问卷ID，避免全表扫描
    WITH AffectedQuestionnaires AS (
        SELECT DISTINCT QuestionnaireID FROM deleted
    ),
    ReorderCTE AS (
        SELECT 
            t.ID,
            t.QuestionOrder,
            ROW_NUMBER() OVER (PARTITION BY t.QuestionnaireID ORDER BY t.QuestionOrder ASC) AS NewOrder
        FROM 你的表名 t
        INNER JOIN AffectedQuestionnaires a ON t.QuestionnaireID = a.QuestionnaireID
    )
    UPDATE ReorderCTE 
    SET QuestionOrder = NewOrder
    WHERE QuestionOrder != NewOrder;
END
GO

注意事项 #

• 如果业务上题目顺序需要按ID、创建时间等其他规则排序，只需将ROW_NUMBER()中的ORDER BY QuestionOrder ASC替换为对应排序字段即可
• 触发器创建后无需额外操作，每次删除记录会自动重置对应分组的连续序号
• 执行批量操作前建议先备份数据，避免意外修改

内容的提问来源于stack exchange，提问作者Naupad Doshi