SQL技术问询：如何为分组行的列批量设置组内连续序号

嗨，这个需求用窗口函数就能完美解决，根本不用手动逐个ID去执行更新，一次性就能批量搞定所有分组的连续序号生成～ 下面给你分几种常用数据库的具体实现方案：

支持窗口函数的数据库（PostgreSQL、MySQL 8.0+、SQL Server等） #

这类数据库直接用ROW_NUMBER()窗口函数，按user_id分组生成序号，然后关联更新即可，这是最简洁高效的方式：

PostgreSQL 写法 #

WITH numbered_rows AS (
    SELECT user_id, 
           -- 按user_id分组，每组内生成连续序号，ORDER BY可替换为你需要的排序字段（比如创建时间）
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id) AS new_index
    FROM my_table
)
UPDATE my_table t
SET "index" = nr.new_index  -- index是关键字，PostgreSQL用双引号括起来避免语法错误
FROM numbered_rows nr
WHERE t.user_id = nr.user_id;

MySQL 8.0+ 写法 #

MySQL的UPDATE语法和PostgreSQL略有不同，用JOIN关联子查询即可：

UPDATE my_table t
JOIN (
    SELECT user_id, 
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id) AS new_index
    FROM my_table
) nr ON t.user_id = nr.user_id
SET t.`index` = nr.new_index;  -- index是关键字，MySQL用反引号括起来

SQL Server 写法 #

WITH numbered_rows AS (
    SELECT user_id, 
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id) AS new_index,
           -- 这里需要唯一标识每行的字段，假设user_id是唯一的，否则加主键字段
           index AS old_index
    FROM my_table
)
UPDATE numbered_rows
SET old_index = new_index;

MySQL 5.x 版本（不支持窗口函数） #

如果你的MySQL版本比较旧，不支持窗口函数，可以用用户变量来实现分组序号：

-- 初始化变量
SET @prev_user = NULL;
SET @row_num = 0;

UPDATE my_table t
JOIN (
    SELECT user_id,
           -- 对比当前user_id和上一个，相同则序号+1，否则重置为1
           @row_num := IF(@prev_user = user_id, @row_num + 1, 1) AS new_index,
           @prev_user := user_id
    FROM my_table
    ORDER BY user_id  -- 必须排序，保证变量逻辑正确
) nr ON t.user_id = nr.user_id
SET t.`index` = nr.new_index;

注意事项 #

• 如果index是数据库的关键字，一定要用反引号（MySQL）或双引号（PostgreSQL）括起来，避免语法错误。
• ORDER BY子句可以根据你的实际需求替换，比如如果需要按创建时间的先后生成序号，就把ORDER BY user_id改成ORDER BY create_time。

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