动态更新SequenceNo列：批量指定与剩余行自动排序SQL方案

原数据表说明 #

现有数据表Maintable，包含rID、SequenceNo两列，示例数据如下：

注：实际行数可为任意N行。

更新需求 #

收到如下更新请求：

• r1需设置SequenceNo为5
• r2需设置SequenceNo为4
• r5需设置SequenceNo为2

要求未指定的行（如示例中的r3、r4）按原顺序分配剩余的连续序号，最终预期结果如下：

现有代码的不足 #

当前通过临时表#apprevisedsequence实现指定行更新的代码如下：

create table #apprevisedsequence
(
mid varchar(10),
appsequence int
)

insert into #apprevisedsequence
select 'r1',5
insert into #apprevisedsequence
select 'r2',4
insert into #apprevisedsequence
select 'r5',2


update a set a.seqno = b.Appsequence
from maintable a join
#apprevisedsequence b on a.rid = b.mid

这段代码仅能完成指定行的序号更新，但未处理未指定行的剩余序号分配。

通用SQL解决方案 #

要实现适配任意行数的需求，我们需要分两步处理：先标记已指定的序号，再为未指定行分配剩余的连续序号。以下是完整脚本：

-- 1. 创建并填充临时存储更新请求的表
create table #apprevisedsequence
(
    mid varchar(10),
    appsequence int
)

insert into #apprevisedsequence
select 'r1',5
insert into #apprevisedsequence
select 'r2',4
insert into #apprevisedsequence
select 'r5',2

-- 2. 生成所有需要分配的序号，并区分已占用和待分配的部分
with all_sequence as (
    -- 生成1到总记录数的连续序号
    select top (select count(*) from Maintable) 
           row_number() over(order by (select null)) as seq
    from sys.columns
),
unassigned_seq as (
    -- 筛选出未被更新请求占用的序号
    select seq from all_sequence
    where seq not in (select appsequence from #apprevisedsequence)
),
unassigned_rows as (
    -- 筛选出未指定更新的行，并保留原顺序
    select rid, row_number() over(order by seqno) as row_num
    from Maintable
    where rid not in (select mid from #apprevisedsequence)
),
final_assignment as (
    -- 为未指定行分配剩余序号
    select u.rid, us.seq as new_seq
    from unassigned_rows u
    join (select seq, row_number() over(order by seq) as row_num from unassigned_seq) us
    on u.row_num = us.row_num
    -- 合并已指定的更新请求
    union all
    select mid as rid, appsequence as new_seq from #apprevisedsequence
)
-- 执行最终更新
update m
set m.seqno = fa.new_seq
from Maintable m
join final_assignment fa on m.rid = fa.rid

-- 清理临时表
drop table #apprevisedsequence

方案说明 #

• all_sequence：生成与原表行数一致的连续序号范围
• unassigned_seq：找出未被更新请求占用的序号
• unassigned_rows：按原seqno顺序标记未指定更新的行
• final_assignment：将未指定行与剩余序号按顺序匹配，再合并已指定的更新记录
• 最后通过关联final_assignment完成全表的序号更新

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