SQL Server：按类别逐行递增更新多行id字段的实现问题

嘿，我完全懂你的困扰——这种按分组递增值的需求确实没法直接用自增主键搞定，而且你原来的SQL语句之所以只给所有行加1，是因为MAX(id)+1在批量更新时是固定值，不会随着每行的更新实时变化，所以同一个类别的所有行都会被设成同一个数。别担心，用窗口函数就能轻松解决这个问题！

核心思路 #

我们需要给每个类别里的行分配一个行号，然后用该类别的起始ID加上行号减1，就能得到逐行递增的ID了。这里的关键是用ROW_NUMBER()窗口函数按类别分组编号，再结合起始值计算新ID。

具体实现（分数据库示例） #

1. SQL Server 写法（用CTE）

假设你的表有一个唯一主键（比如idStyle，从你的原语句里看到的），可以用公共表表达式（CTE）先计算出每个行的新ID，再批量更新：

WITH CategoryRowNumbers AS (
    SELECT 
        id,
        idStyle, -- 唯一主键，用来定位每行
        -- 按类别分组，获取该类别的起始ID（这里用该类别的最小ID作为起始，如果你有固定起始值可以替换）
        MIN(id) OVER (PARTITION BY category) 
        -- 给每个类别内的行编号，从1开始
        + ROW_NUMBER() OVER (PARTITION BY category ORDER BY idStyle) - 1 AS new_id
    FROM STYLE
)
UPDATE CategoryRowNumbers
SET id = new_id;

2. MySQL 8.0+ 写法（用JOIN）

MySQL支持窗口函数后，可以用子查询计算新ID，再通过主键关联更新：

UPDATE STYLE t1
JOIN (
    SELECT 
        idStyle,
        MIN(id) OVER (PARTITION BY category) 
        + ROW_NUMBER() OVER (PARTITION BY category ORDER BY idStyle) - 1 AS new_id
    FROM STYLE
) t2 ON t1.idStyle = t2.idStyle
SET t1.id = t2.new_id;

自定义起始值的情况 #

如果每个类别的起始ID是固定值（比如类别1必须从1000开始，类别2从2000开始），把MIN(id)换成CASE语句即可：

-- 以SQL Server为例，其他数据库同理
WITH CategoryRowNumbers AS (
    SELECT 
        id,
        idStyle,
        CASE category
            WHEN 1 THEN 1000
            WHEN 2 THEN 2000
            WHEN 3 THEN 3000 -- 可扩展更多类别
        END + ROW_NUMBER() OVER (PARTITION BY category ORDER BY idStyle) - 1 AS new_id
    FROM STYLE
)
UPDATE CategoryRowNumbers
SET id = new_id;

关键注意事项 #

• 必须有唯一标识列：比如idStyle，用来确保每行都能被精准定位，避免更新错乱。
• 排序规则：ORDER BY idStyle是为了保证行号的顺序固定，如果你需要按其他逻辑排序（比如创建时间），替换成对应的列即可。如果没有特定顺序，用ORDER BY (SELECT NULL)也可以，但不同数据库可能有差异。
• 批量更新的特性：数据库的批量更新是基于更新前的数据计算的，所以原来的MAX(id)+1只会取一次最大值，导致所有行都用同一个值，这就是你之前语句失效的原因。

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