如何用单条PostgreSQL查询获取所有Foo及其最新关联Bar？

现有foos表和bars表，一个Foo对应多个Bar，需通过单条PostgreSQL查询返回所有Foo及其对应的最新Bar。

表结构 #

foos表 #

bars表 #

预期结果 #

解决方案：窗口函数法（推荐） #

利用PostgreSQL的ROW_NUMBER()窗口函数，按foo_id分组并按创建时间倒序排序，筛选出每组的第一条（最新）记录，再与foos表关联：

SELECT 
    f.id,
    f.name,
    b.id AS "bar.id",
    b.foo_id AS "bar.foo_id",
    b.created_date AS "bar.created_date"
FROM foos f
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY foo_id ORDER BY created_date DESC) AS rn
    FROM bars
) b ON f.id = b.foo_id AND b.rn = 1;

说明

• 子查询中，PARTITION BY foo_id将bars按所属Foo分组，ORDER BY created_date DESC让组内记录从新到旧排序，ROW_NUMBER()为每组记录分配序号，最新记录序号为1。
• 外层通过LEFT JOIN关联，仅保留序号为1的记录，确保每个Foo对应最新的Bar；若只需返回有对应Bar的Foo，可替换为INNER JOIN。
• 若存在多条Bar创建时间相同且均为最新的情况，ROW_NUMBER()会随机取一条，若需保留所有最新记录，可改用RANK()函数。

备选方案：关联子查询法 #

SELECT 
    f.id,
    f.name,
    b.id AS "bar.id",
    b.foo_id AS "bar.foo_id",
    b.created_date AS "bar.created_date"
FROM foos f
LEFT JOIN bars b ON f.id = b.foo_id
WHERE b.created_date = (
    SELECT MAX(created_date) FROM bars WHERE foo_id = f.id
);

说明

• 此方法通过子查询获取每个Foo对应的最新created_date，再关联筛选出对应记录。
• 缺点：若存在多条Bar创建时间相同且为最新，会返回多条记录；数据量大时性能不如窗口函数法。

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