如何在PostgreSQL中更新表，用窗口函数结果填充order_column列？

这个需求我经常碰到，核心就是先通过窗口函数算出每一行该有的排序值，再把这个值同步到你新增的order_column里就行。不同数据库的写法略有差异，我给你整理几个主流的实现方式：

1. PostgreSQL / Oracle #

可以借助CTE（公共表表达式）先计算出排序值，再关联原表进行更新：

WITH ranked_rows AS (
    SELECT 
        id, -- 替换成你的表的**主键或唯一标识列**，确保行匹配正确
        RANK() OVER (PARTITION BY column1 ORDER BY column2) AS new_order
    FROM mytable
)
UPDATE mytable
SET order_column = ranked_rows.new_order
FROM ranked_rows
WHERE mytable.id = ranked_rows.id;

2. MySQL 8.0+ #

MySQL 8.0及以上版本支持窗口函数，用JOIN关联子查询的方式更新即可：

UPDATE mytable t1
JOIN (
    SELECT 
        id, -- 同样需要主键/唯一键来关联
        RANK() OVER (PARTITION BY column1 ORDER BY column2) AS new_order
    FROM mytable
) t2 ON t1.id = t2.id
SET t1.order_column = t2.new_order;

3. SQL Server #

可以用CTE或者直接JOIN子查询的方式更新，两种写法都可行：

写法一（CTE） #

WITH ranked_rows AS (
    SELECT 
        id,
        RANK() OVER (PARTITION BY column1 ORDER BY column2) AS new_order
    FROM mytable
)
UPDATE t
SET order_column = rr.new_order
FROM mytable t
JOIN ranked_rows rr ON t.id = rr.id;

写法二（直接JOIN子查询） #

UPDATE t
SET order_column = rr.new_order
FROM mytable t
JOIN (
    SELECT id, RANK() OVER (PARTITION BY column1 ORDER BY column2) AS new_order
    FROM mytable
) rr ON t.id = rr.id;

关键注意事项 #

• 一定要用主键或唯一键关联原表和计算结果，避免出现行匹配错误导致的更新异常。
• 如果需要连续无重复的行号（比如不需要并列排名），可以把RANK()替换成ROW_NUMBER()，根据你的业务需求选择合适的窗口函数。
• 更新前建议先单独执行子查询/CTE的SELECT部分，确认new_order的值符合预期，再执行UPDATE操作，避免误更新数据。

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