SQL Server中删除记录后重置分组优先级的SQL实现方案

现有TestRule表中，每条记录关联template_id，原本每个template_id分组内的priority为连续递增序列。在删除rule_id为2和5的记录后，部分分组的priority出现断层（例如template_id=100下的priority变为1、3），可通过以下SQL高效重置分组内的priority为连续的1、2、3...，无需后端处理：

全局重置所有分组的优先级 #

WITH RankedRules AS (
    SELECT 
        rule_id,
        ROW_NUMBER() OVER (PARTITION BY template_id ORDER BY priority) AS new_priority
    FROM TestRule
)
UPDATE TestRule
SET priority = rr.new_priority
FROM TestRule tr
INNER JOIN RankedRules rr ON tr.rule_id = rr.rule_id;

仅重置创建日期超一周的记录 #

如果只需处理创建日期超过一周的记录，可在CTE中添加筛选条件：

WITH RankedRules AS (
    SELECT 
        rule_id,
        ROW_NUMBER() OVER (PARTITION BY template_id ORDER BY priority) AS new_priority
    FROM TestRule
    WHERE create_date < DATEADD(day, -7, GETDATE())
)
UPDATE TestRule
SET priority = rr.new_priority
FROM TestRule tr
INNER JOIN RankedRules rr ON tr.rule_id = rr.rule_id;

说明 #

• 利用ROW_NUMBER()窗口函数，按template_id分组、原priority排序生成连续序号，确保新优先级与原顺序一致
• 通过CTE关联原表更新，避免多次扫描表，效率优于后端循环处理方案

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