如何实现priority_list表优先级修改后的自动重排？

首先，你当前的SQL语句存在几个核心问题：

1. 逻辑错误：子查询select priority from priority_list where priority=I_Priority返回的是当前已存在该优先级的记录值，导致你错误修改了所有小于该值的记录优先级，还会造成优先级重复。
2. 未处理目标记录：你的语句没有将目标记录（比如D）的优先级更新为指定的I_Priority。
3. 未区分优先级移动方向：修改优先级时需要区分是往前移（新优先级 < 原优先级）还是往后移（新优先级 > 原优先级），不同方向的调整逻辑完全不同。

常规需求解决方案（保持相对顺序的连续优先级） #

如果你的真实需求是：修改某条记录的优先级后，所有记录的优先级保持唯一连续的整数（1~N），且其他记录的相对顺序符合移动逻辑（比如把D从4移到3，C自动后移到4），可以使用以下存储过程：

存储过程代码（以SQL Server为例） #

CREATE PROCEDURE UpdatePriority
    @new_priority INT,
    @target_header VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 获取目标记录的原优先级
    DECLARE @old_priority INT;
    SELECT @old_priority = Priority 
    FROM priority_list 
    WHERE Header = @target_header;

    -- 校验记录是否存在
    IF @old_priority IS NULL
    BEGIN
        RAISERROR('指定Header的记录不存在', 16, 1);
        RETURN;
    END

    -- 原优先级与新优先级相同，无需操作
    IF @old_priority = @new_priority
        RETURN;

    -- 2. 根据移动方向调整其他记录的优先级
    IF @new_priority < @old_priority
    BEGIN
        -- 往前移：将新优先级到原优先级-1的记录优先级+1，腾出位置
        UPDATE priority_list
        SET Priority = Priority + 1
        WHERE Priority BETWEEN @new_priority AND @old_priority - 1;
    END
    ELSE
    BEGIN
        -- 往后移：将原优先级+1到新优先级的记录优先级-1，腾出位置
        UPDATE priority_list
        SET Priority = Priority - 1
        WHERE Priority BETWEEN @old_priority + 1 AND @new_priority;
    END

    -- 3. 更新目标记录的优先级
    UPDATE priority_list
    SET Priority = @new_priority
    WHERE Header = @target_header;
END

执行示例 #

将D的优先级从4改为3：

EXEC UpdatePriority @new_priority = 3, @target_header = 'D';

执行后结果：

Priority  Header
  1         A       
  2         B         
  3         D         
  4         C
  5         E

针对你给出的特殊示例的解决方案 #

如果你的需求确实是修改D的优先级到3后，得到你给出的特殊结果（A变为5，B1、C2、D3、E4），这说明你的重排逻辑是除指定记录外，所有记录优先级减1，原最小优先级的记录移到最大位置。这种逻辑可以通过以下代码实现：

CREATE PROCEDURE UpdatePriority_Special
    @new_priority INT,
    @target_header VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取记录总数
    DECLARE @total_count INT;
    SELECT @total_count = COUNT(*) FROM priority_list;

    -- 1. 更新目标记录的优先级
    UPDATE priority_list
    SET Priority = @new_priority
    WHERE Header = @target_header;

    -- 2. 所有非目标记录的优先级减1，最小优先级的记录设为总数
    UPDATE priority_list
    SET Priority = CASE 
                    WHEN Priority = 1 THEN @total_count
                    ELSE Priority - 1
                  END
    WHERE Header != @target_header;
END

执行该存储过程后，会得到你给出的示例结果，但这种逻辑和“修改优先级到指定值”的关联较弱，建议你确认需求是否准确。

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