PostgreSQL如何对表指定列排序并将值更新为排序后结果

错误写法原因 #

你的原有写法不成立：子查询返回多行结果，无法直接给单行字段赋值，且关系型数据库的表是无序集合，直接排序子查询无法和原表行一一对应。

正确实现逻辑 #

通过窗口函数给原表行和排序后的数值分别生成对应行号，按行号关联后批量更新，即可实现你要的按升序替换整列值的效果。

前提：你的表需要有唯一标识每行的字段（例如下方示例用的主键 id），如果没有可以先新增临时自增列，更新完成后删除即可。

示例代码（适配你给出的t1表points列场景） #

WITH original_rows AS (
    -- 给原表每行按主键顺序生成行号
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM t1
),
sorted_points AS (
    -- 给排序后的points值从小到大生成行号
    SELECT points, ROW_NUMBER() OVER (ORDER BY points ASC) AS rn
    FROM t1
)
-- 按行号关联更新
UPDATE t1
SET points = sorted_points.points
FROM original_rows
JOIN sorted_points ON original_rows.rn = sorted_points.rn
WHERE t1.id = original_rows.id;

适配你代码中outputTable表points_count列的版本 #

WITH original_rows AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM outputTable
),
sorted_values AS (
    SELECT points_count, ROW_NUMBER() OVER (ORDER BY points_count ASC) AS rn
    FROM outputTable
)
UPDATE outputTable
SET points_count = sorted_values.points_count
FROM original_rows
JOIN sorted_values ON original_rows.rn = sorted_values.rn
WHERE outputTable.id = original_rows.id;

注意事项 #

• 执行更新前建议先开启事务执行，验证结果符合预期再提交，避免误改数据：

  BEGIN;
  -- 执行上面的UPDATE语句
  -- 验证更新后结果是否正确
  SELECT * FROM outputTable ORDER BY id;
  -- 没问题就提交，有问题就回滚
  -- COMMIT;
  -- ROLLBACK;
  

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