如何基于id和country列更新表的row_number字段？

问题分析 #

你的需求是按id和country分组，对每组内的记录按date升序生成连续的行号。两种尝试方法的问题如下：

• 第一种方法：子查询条件逻辑错误，WHERE country = f.country AND id = f.id实际是统计当前分组的总记录数，导致所有行的row_number值相同，无法生成逐行递增的序号。
• 第二种方法：JOIN仅匹配country和id，同一分组内的多条记录会互相匹配，造成重复更新，结果混乱。

正确解决方案 #

方案1：直接查询生成行号（无需更新表） #

如果只是需要获取带行号的查询结果，直接使用ROW_NUMBER()窗口函数即可，这是最简洁高效的方式：

SELECT 
    id,
    country,
    date,
    ROW_NUMBER() OVER (PARTITION BY id, country ORDER BY date ASC) AS row_number
FROM [pay].[f_lending_net_margin]
ORDER BY id, country, date;

方案2：更新表中的row_number字段 #

如果必须要更新表内的row_number字段，需要确保匹配条件能唯一定位到每一行，避免重复匹配。推荐使用CTE（公共表表达式）结合窗口函数实现：

WITH ranked_data AS (
    SELECT 
        id,
        country,
        date,
        row_number,
        ROW_NUMBER() OVER (PARTITION BY id, country ORDER BY date ASC) AS new_row_number
    FROM [pay].[f_lending_net_margin]
)
UPDATE ranked_data
SET row_number = new_row_number;

如果你的表存在唯一主键（比如record_id），也可以用JOIN方式，通过唯一键精准匹配：

UPDATE f
SET f.row_number = x.new_row_number
FROM [pay].[f_lending_net_margin] AS f
INNER JOIN (
    SELECT 
        record_id, -- 替换为你的表的唯一主键字段
        ROW_NUMBER() OVER (PARTITION BY id, country ORDER BY date ASC) AS new_row_number
    FROM [pay].[f_lending_net_margin]
) AS x ON f.record_id = x.record_id;

关键说明 #

• PARTITION BY id, country：指定按id和country分组，每组独立生成行号
• ORDER BY date ASC：确保每组内的行号按日期升序排列，和你期望的结果一致
• 更新操作必须保证匹配条件能唯一识别每一行，否则会出现重复更新或数据错误

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