PostgreSQL左连接LEFT JOIN时如何仅取关联表最新1条数据？

方案1：窗口函数实现（推荐，适配MySQL 8.0+、PostgreSQL、Oracle等所有支持窗口函数的现代数据库） #

仅需单次扫描payout_ledgers表即可完成所有最新记录的筛选，避免逐行执行子查询的性能损耗：

SELECT SUM(pl.achieved)
FROM payout p
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY payout_id ORDER BY ts DESC, id DESC) AS rn
    FROM payout_ledgers
) pl ON pl.payout_id = p.id AND pl.rn = 1
-- 可在此处追加你原业务所需的WHERE、ORDER BY、LIMIT条件

说明：排序规则追加id DESC是为了避免同一payout存在多条ts完全相同的记录时，结果出现不确定性

方案2：低版本数据库兼容实现（如MySQL 5.x等不支持窗口函数的场景） #

通过分组取最大ID的方式筛选最新记录：

SELECT SUM(pl.achieved)
FROM payout p
LEFT JOIN (
    SELECT pl1.*
    FROM payout_ledgers pl1
    INNER JOIN (
        SELECT payout_id, MAX(id) AS latest_id
        FROM payout_ledgers
        GROUP BY payout_id
    ) pl2 ON pl1.id = pl2.latest_id
) pl ON pl.payout_id = p.id

说明：直接取MAX(id)比取MAX(ts)性能更好，且自增ID天然对应最新写入的记录，不会出现重复冲突

• 第一种关联子查询方案性能差的核心原因是相关子查询逐行执行：每扫描1条payout记录就要执行1次子查询，数据量越大执行次数越多，性能损耗线性叠加。
• 第二种WHERE条件子查询方案报错的原因是：WHERE层的子查询无法引用FROM子句中定义的表别名pl，超出了子查询的作用域范围。

• 索引优化（无需修改表结构，生产环境可快速落地）：给payout_ledgers表创建联合索引 (payout_id, id DESC, achieved)，该索引为覆盖索引，子查询可直接通过索引返回所需全部字段，无需回表查询，查询性能可提升至接近原生无关联查询的水平。
• 架构优化（后续可调整schema时参考）：在payout表新增latest_ledger_id冗余字段，每次写入新的payout_ledgers记录时同步更新该字段，查询时直接通过该字段关联payout_ledgers即可，性能最优。

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