如何通过SQL遍历子记录为每个主表关联的明细生成递增Position值

支持窗口函数的数据库通用方案（适配MySQL 8.0+、SQL Server、PostgreSQL、Oracle） #

核心是通过ROW_NUMBER()窗口函数按MasterID分组、明细ID排序生成连续序号，再关联更新原表：

SQL Server / PostgreSQL 写法 #

WITH RankedDetails AS (
    SELECT
        ID,
        ROW_NUMBER() OVER (PARTITION BY MasterID ORDER BY ID ASC) AS NewPosition
    FROM "Detail-Table" -- 表名含特殊字符需用对应数据库标识符包裹，SQL Server用[Detail-Table]
)
UPDATE d
SET d.Position = r.NewPosition
FROM "Detail-Table" d
INNER JOIN RankedDetails r ON d.ID = r.ID
WHERE d.Position = 0; -- 可选：仅更新未赋值的记录，避免重复执行覆盖已有数据

MySQL 8.0 写法 #

UPDATE `Detail-Table` d
INNER JOIN (
    SELECT
        ID,
        ROW_NUMBER() OVER (PARTITION BY MasterID ORDER BY ID ASC) AS NewPosition
    FROM `Detail-Table`
) r ON d.ID = r.ID
SET d.Position = r.NewPosition
WHERE d.Position = 0;

MySQL 5.x 兼容写法（无窗口函数支持场景） #

通过用户变量实现分组排序计数：

SET @prev_master_id = 0, @current_pos = 1;
UPDATE `Detail-Table`
SET Position = IF(
    MasterID = @prev_master_id,
    @current_pos := @current_pos + 1,
    @current_pos := 1 AND @prev_master_id := MasterID
)
ORDER BY MasterID ASC, ID ASC;

注意事项 #

• 执行前请先备份明细表数据，避免误操作导致数据丢失
• 数据量过大时可按MasterID范围分批执行更新，减少锁表时长影响业务
• 表名包含-等特殊字符时，需按数据库规则添加标识符包裹：MySQL用反引号`、SQL Server用方括号[]、PostgreSQL/Oracle用双引号"

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