如何使用row_number()函数更新数据表中的ID列？

我有一张包含user_id和user_name列的数据表，还有一个ID列需要填充，填充规则如下表所示：

需求是识别唯一的user_id及其出现次数，为每个user_id的每条记录分配从1到n的序号。我已经通过以下SQL语句得到了序号列seq：

select row_number() over (partition by user_id order by user_id) as seq, user_id, user_name from test_csv

现在需要把数据表中的ID列更新为上述查询得到的seq值，以下是不同数据库环境下的实现方法：

不同数据库的更新方案 #

MySQL（8.0及以上版本） #

MySQL 8.0支持窗口函数，你可以用CTE结合关联更新，或者临时表的方式：

方法1：使用CTE

WITH ranked_data AS (
    SELECT 
        user_id,
        user_name,
        row_number() over (partition by user_id order by user_id) as seq,
        ctid AS row_identifier -- MySQL 8.0.19+支持的内部行标识，有主键的话替换为主键列更稳妥
    FROM test_csv
)
UPDATE test_csv t
JOIN ranked_data rd ON t.ctid = rd.row_identifier
SET t.ID = rd.seq;

方法2：使用临时表

如果你的MySQL版本不支持CTE，或者更习惯临时表操作：

-- 创建临时表存储序号数据
CREATE TEMPORARY TABLE temp_rankings AS
SELECT 
    user_id,
    user_name,
    row_number() over (partition by user_id order by user_id) as seq,
    ctid AS row_id
FROM test_csv;

-- 关联更新原表
UPDATE test_csv t
JOIN temp_rankings tr ON t.ctid = tr.row_id
SET t.ID = tr.seq;

-- 删除临时表
DROP TEMPORARY TABLE temp_rankings;

SQL Server #

SQL Server支持直接在CTE中更新数据，写法更简洁：

WITH ranked_data AS (
    SELECT 
        ID,
        row_number() over (partition by user_id order by user_id) as seq
    FROM test_csv
)
UPDATE ranked_data
SET ID = seq;

PostgreSQL #

PostgreSQL可以利用内部行标识符ctid或者主键来关联更新：

WITH ranked_data AS (
    SELECT 
        ctid,
        row_number() over (partition by user_id order by user_id) as seq
    FROM test_csv
)
UPDATE test_csv t
SET ID = rd.seq
FROM ranked_data rd
WHERE t.ctid = rd.ctid;

如果表有主键，建议用主键替代ctid，避免因行存储变化导致的匹配问题：

WITH ranked_data AS (
    SELECT 
        your_primary_key,
        row_number() over (partition by user_id order by user_id) as seq
    FROM test_csv
)
UPDATE test_csv t
SET ID = rd.seq
FROM ranked_data rd
WHERE t.your_primary_key = rd.your_primary_key;

关键注意点 #

• 确定排序规则：当前order by user_id在同一user_id分组内的行顺序是不确定的，若需要固定序号的分配逻辑，建议添加一个能唯一确定顺序的列（比如记录创建时间、主键等），避免每次执行后序号出现波动。
• 精确匹配行：如果表中存在重复的user_id+user_name组合且无唯一主键，必须确保关联条件能精准匹配到对应行，否则可能出现错误更新。

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