PostgreSQL：如何查询物品的最新描述及关联表字段

表结构与业务逻辑 #

现有两张PostgreSQL表：

CREATE TABLE items 
(
    item_id bigint BIGSERIAL,
    description character varying(2000) NOT NULL,
    date TIMESTAMP default now(),
    -- (many more columns)
    CONSTRAINT "item_pk" PRIMARY KEY ("item_id")
);

CREATE TABLE item_updates 
(
    update_id bigint BIGSERIAL,
    item_id_fk bigint NOT NULL,
    date TIMESTAMP default now(),
    -- (more columns)
    description character varying(2000) NOT NULL,

    CONSTRAINT "item_updates_pk" 
        PRIMARY KEY ("update_id"),
    CONSTRAINT "item_updates_item_fk" 
        FOREIGN KEY (item_id_fk) REFERENCES items(item_id) NOT DEFERRABLE,
);

业务逻辑：

• 新增物品时写入items表并设置description；
• 描述变更时，在item_updates表插入关联item_id的记录以保留历史。

查询需求 #

• 无更新记录的item_id，返回items表的description及其他字段；
• 有更新记录的item_id，返回item_updates表中最新的description及该表其他字段，同时关联items表对应字段；
• 查询结果需包含所有item_id及对应字段。

原查询错误分析 #

原查询存在两个核心问题：

• 子查询sub中未包含description字段，外层COALESCE调用自然无法识别该字段；
• MAX(upd.item_id)逻辑错误，item_id是items表的主键，item_updates中对应的关联字段是item_id_fk，应该用MAX(upd.update_id)（自增主键，最新的update_id对应最新记录）或MAX(upd.date)来获取每个物品的最新更新记录。

正确查询方案 #

方案1：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数为每个物品的更新记录排序，取最新的一条后与items表左连接：

SELECT
    -- 优先取更新表的最新描述，无更新则取物品表的描述
    COALESCE(upd.description, i.description) AS description,
    -- 按需选择返回字段，避免字段名冲突（如两张表的date字段需加别名）
    i.item_id, i.date AS item_create_date, 
    upd.update_id, upd.date AS update_date, upd.description AS latest_description
    -- 若有其他需要返回的字段，按需添加
FROM
    items i
LEFT JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY item_id_fk ORDER BY date DESC, update_id DESC) AS rn
    FROM
        item_updates
) upd ON i.item_id = upd.item_id_fk AND upd.rn = 1;

说明：

• PARTITION BY item_id_fk按物品ID分组；
• ORDER BY date DESC, update_id DESC确保同一时间有多条更新时，取自增ID最大的最新插入记录；
• upd.rn = 1仅保留每个物品的最新更新记录。

方案2：关联子查询获取最新更新ID #

先通过子查询找到每个物品的最新update_id，再关联item_updates和items表：

SELECT
    COALESCE(upd.description, i.description) AS description,
    i.*,
    upd.*
FROM
    items i
LEFT JOIN item_updates upd ON i.item_id = upd.item_id_fk
AND upd.update_id = (
    SELECT MAX(update_id)
    FROM item_updates
    WHERE item_id_fk = i.item_id
);

说明：

• 子查询(SELECT MAX(update_id) ...)获取每个物品的最新更新记录ID；
• 左连接确保无更新记录的物品也会被完整返回。

注意事项 #

• 若两张表存在同名字段（如date），必须给字段添加别名区分，避免查询结果混乱；
• 优先使用update_id作为排序/取最大的依据，因为自增主键能保证唯一性，而date字段可能存在重复值。

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