在关系型数据库的日常运维中，序列号（serial number）往往被用作业务单据号、流水号或展示顺序的依据。当部分记录被物理删除后，原本连续的编号会出现空洞，影响报表展示与对账。借助SQL标准中的窗口函数，尤其是ROW_NUMBER，可以在不改变底层存储的前提下，于查询层重新计算出一套连续、无断点的逻辑序列号，从而透明地修复展示层面的缺失问题。
ROW_NUMBER窗口函数的基本重排原理
ROW_NUMBER是SQL:2003引入的窗口函数之一，它为分区内的每一行按照ORDER BY子句指定的顺序分配一个从1开始的唯一整数。与自增列不同，ROW_NUMBER并不持久化，而是在每次执行查询时动态计算，因此非常适合用来“覆盖”那些已经丢失的物理序列。其核心语法为ROW_NUMBER() OVER (PARTITION BY 列 ORDER BY 列)，其中OVER子句定义了窗口范围与排序规则。
假设有一张业务表orders，包含字段id（物理主键）、biz_no（原序列号，INT类型）与create_time。由于历史删除，biz_no从1、2、4、5、8这样断开。我们希望在查询时得到一个连续的new_no。以下语句即可实现：
SELECT
id,
biz_no,
ROW_NUMBER() OVER (ORDER BY create_time ASC) AS new_no
FROM orders
ORDER BY create_time ASC;
上述代码按create_time升序重排，生成的new_no严格连续。需要注意，如果create_time存在重复值，ROW_NUMBER会随机分配次序，因此在生产环境建议用唯一键（如id）作为次要排序列，写成ORDER BY create_time ASC, id ASC，避免每次查询次序漂移。
利用CTE与临时表两种方案修复并回写
如果仅需在报表中展示连续号，上一节的查询已足够；但若要将修复后的编号真正回写到原表（例如补填biz_no），则需要借助公用表表达式（CTE）或临时表完成更新。CTE写法更为简洁，能在单条语句中完成“计算新号+关联更新”的动作，可读性高，且在PostgreSQL、SQL Server、MySQL 8.0中均被支持。
WITH ranked AS (
SELECT
id,
ROW_NUMBER() OVER (ORDER BY create_time ASC, id ASC) AS new_no
FROM orders
)
UPDATE orders o
SET biz_no = r.new_no
FROM ranked r
WHERE o.id = r.id;
相对地，临时表方案先将重排结果落入#tmp_order，再执行UPDATE。该方式在超大数据量（如千万级）场景下便于分段提交，也能在更新前对new_no做校验，但会多一次IO。示例代码如下：
SELECT
id,
ROW_NUMBER() OVER (ORDER BY create_time ASC, id ASC) AS new_no
INTO #tmp_order
FROM orders;
UPDATE o
SET biz_no = t.new_no
FROM orders o
JOIN #tmp_order t ON o.id = t.id;
DROP TABLE #tmp_order;
从执行计划看，CTE通常被优化器内联处理，减少了物化开销；临时表则在复杂校验时更灵活。实际选型应结合事务长度与锁持有时间，避免大事务阻塞线上读写。
常见误区与字符串序列号的重排陷阱
一个高频错误是将biz_no定义为VARCHAR，并直接按该列排序重排。由于字符串比较遵循字典序，编号“10”会排在“2”之前，导致ROW_NUMBER给出的新序列在业务上依然错乱。此时必须显式转换为整数再排序，或使用LPAD补零使字典序与数值序一致。
-- 错误示范：字符串排序导致10在2前 SELECT biz_no, ROW_NUMBER() OVER (ORDER BY biz_no) AS new_no FROM orders; -- 正确示范：转为整数 SELECT biz_no, ROW_NUMBER() OVER (ORDER BY CAST(biz_no AS INT)) AS new_no FROM orders;
另一个容易被忽视的点是并发写入。若修复脚本运行的同时仍有新单据插入，且新单使用的仍是旧的最大物理号，就可能和重排后的逻辑号冲突。因此，回写类操作应在低峰期加表锁，或采用“逻辑号仅用于查询、物理号保留断点”的最终一致方案，从架构上规避锁竞争。
此外，当表存在多租户字段时，必须加上PARTITION BY tenant_id，否则不同租户的序列会混排，造成编号越界。合理使用分区窗口，才能让ROW_NUMBER在每个业务域内独立重排，真正达成“自动修复丢失序列号”的目标。
SQL窗口函数ROW_NUMBER序列号修复修改时间：2026-08-18 09:58:24