MySQL：如何用ROW_NUMBER构建序列，保留sort_order位置并按name排序？

要实现让有sort_order值的记录固定在指定位置，无值记录按name升序填充剩余连续位置的需求，你需要先排除已被占用的位置，再为无值记录分配可用位置，而不是直接用全表行号替换NULL。以下是具体实现：

完整SQL语句 #

WITH RECURSIVE nums AS (
    -- 生成1到总记录数的连续数字序列
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < (SELECT COUNT(*) FROM entries)
),
used_positions AS (
    -- 收集已被使用的sort_order值
    SELECT DISTINCT sort_order FROM entries WHERE sort_order IS NOT NULL
),
available_positions AS (
    -- 筛选出未被占用的位置，并为可用位置编号
    SELECT 
        n AS pos,
        ROW_NUMBER() OVER (ORDER BY n ASC) AS rn
    FROM nums 
    WHERE n NOT IN (SELECT sort_order FROM used_positions)
),
null_sort_entries AS (
    -- 筛选无sort_order的记录，按name升序排序并编号
    SELECT 
        name,
        ROW_NUMBER() OVER (ORDER BY name ASC) AS rn
    FROM entries 
    WHERE sort_order IS NULL
),
assigned_null_entries AS (
    -- 将可用位置按顺序分配给无sort_order的记录
    SELECT 
        nse.name,
        ap.pos AS sort_order
    FROM null_sort_entries nse
    JOIN available_positions ap ON nse.rn = ap.rn
),
all_entries AS (
    -- 合并两类记录：原有sort_order的记录 + 已分配位置的无sort_order记录
    SELECT name, sort_order FROM entries WHERE sort_order IS NOT NULL
    UNION ALL
    SELECT name, sort_order FROM assigned_null_entries
)
-- 最终按sort_order升序查询
SELECT name, sort_order 
FROM all_entries 
ORDER BY sort_order ASC;

逻辑说明 #

1. 生成连续位置序列：通过递归CTE nums 创建1到总记录数的连续数字，覆盖所有可能的位置。
2. 标记已用位置：used_positions 收集所有已存在的sort_order值，避免重复分配。
3. 分配可用位置：available_positions 筛选出未被占用的位置并编号，null_sort_entries 按name升序给无值记录编号，两者关联后完成位置分配。
4. 合并并排序：合并两类记录后按sort_order升序排列，得到你期望的结果。

执行结果 #

运行上述SQL后，将得到与你期望完全一致的输出：

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