PostgreSQL如何为满足条件的行更新从0递增的sort_position列

嘿，要在PostgreSQL里实现你说的这个需求——把userz表中所有type为customer的行的sort_position从0开始依次递增，其实用窗口函数就能搞定，毕竟PostgreSQL不支持MySQL那种直接用会话变量做自增赋值的写法。下面是具体的实现方案：

方案一：用CTE（公共表表达式）实现 #

这是比较清晰易读的写法，先通过CTE算出每个符合条件的行对应的新排序值，再更新原表：

WITH ranked_customers AS (
    SELECT 
        id,
        -- 用ROW_NUMBER生成从1开始的序号，减1后得到从0开始的递增序列
        ROW_NUMBER() OVER (ORDER BY sort_position) - 1 AS new_sort_pos
    FROM userz
    WHERE type = 'customer'
)
UPDATE userz u
SET sort_position = rc.new_sort_pos
FROM ranked_customers rc
WHERE u.id = rc.id;

方案二：用子查询实现 #

如果你更习惯子查询的写法，也可以这样写，效果和上面完全一样：

UPDATE userz u
SET sort_position = sub.new_sort_pos
FROM (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY sort_position) - 1 AS new_sort_pos
    FROM userz
    WHERE type = 'customer'
) sub
WHERE u.id = sub.id;

关键细节说明： #

• 排序依据：ROW_NUMBER() OVER (ORDER BY sort_position)是按照原来的sort_position值对customer类型的行排序，这样生成的新序号会和你原来的顺序保持一致，和MySQL中执行那条UPDATE后的结果完全匹配。
• PostgreSQL的更新语法：这里用了UPDATE ... FROM的写法，这是PostgreSQL中更新关联查询结果的标准方式，用来关联子查询/CTE和原表，确保只更新符合条件的行。

执行后的效果验证 #

跑完上面的语句后，你的userz表会变成这样：

+----+---------------+----------+
| id | sort_position | type     |
+----+---------------+----------+
| 1  | -5            | admin    |
| 2  | 0             | customer |
| 3  | 1             | customer |
| 4  | 8             | employee |
| 5  | 2             | customer |
+----+---------------+----------+

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