如何在PostgreSQL的UPDATE JOIN中使用ORDER BY与LIMIT

嘿，这个坑我之前踩过！你遇到的错误是因为PostgreSQL在处理UPDATE ... FROM这种关联更新时，直接在主语句里加ORDER BY和LIMIT 1的写法不适用——这个LIMIT会作用于整个B表，而不是每个A表记录对应的B表分组，而且PostgreSQL的底层逻辑会用类似DISTINCT ON的方式去重，但你的排序字段没和分组字段对应上，所以触发了这个报错。

下面给你两种靠谱的解决方法，都是先把B表中每个some_id对应的目标记录（z最小的那条）提前筛选出来，再关联A表更新：

方法1：用DISTINCT ON筛选目标记录 #

这是PostgreSQL特有的简洁写法，适合简单的分组取数场景：

UPDATE a
SET x = b.x, y = b.y
FROM (
    -- 按some_id分组，每组只保留z最小的那条记录
    SELECT DISTINCT ON (some_id) some_id, x, y
    FROM b
    -- 必须先按分组字段排序，再按你要的规则排序
    ORDER BY some_id, z ASC
) AS b
WHERE a.some_id = b.some_id;

DISTINCT ON (some_id)会把B表按some_id分组，每组只保留ORDER BY里排在最前面的那条记录——这里先按some_id排序保证分组，再按z ASC取每组中z最小的行。

方法2：用ROW_NUMBER()窗口函数（更灵活） #

如果以后需要更复杂的筛选规则（比如多个排序条件、过滤特定状态），窗口函数的写法扩展性更好：

UPDATE a
SET x = b.x, y = b.y
FROM (
    SELECT 
        some_id, x, y,
        -- 按some_id分组，组内按z升序给行编号
        ROW_NUMBER() OVER (PARTITION BY some_id ORDER BY z ASC) AS rn
    FROM b
) AS b
-- 只取每组的第一条记录（z最小的）
WHERE a.some_id = b.some_id AND b.rn = 1;

PARTITION BY some_id把B表拆分成多个分组，ORDER BY z ASC定义组内排序规则，ROW_NUMBER()给每组的行从1开始编号，最后只取rn=1的行来关联更新。

这两种写法都能完美解决你遇到的问题，选哪种取决于你的实际需求复杂度~

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