PostgreSQL中基于关联表最新记录的表连接实现

要给每个订单匹配其最新的支付记录（包括退款状态），可以用以下两种高效的方法：

方法一：使用窗口函数ROW_NUMBER() #

通过窗口函数为每个订单的支付记录按日期、ID排序并标记行号，取行号为1的记录就是最新的支付记录：

SELECT o.*, p.status AS latest_payment_status, p.date AS latest_payment_date
FROM orders o
LEFT JOIN (
    SELECT 
        *,
        -- 按订单分组，先按日期倒序，日期相同则按ID倒序（确保取最新的那条）
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY date DESC, id DESC) AS rn
    FROM payments
) p ON o.id = p.order_id AND p.rn = 1;

• PARTITION BY order_id：将支付记录按订单ID分组
• ORDER BY date DESC, id DESC：每组内优先按支付日期从新到旧排序，日期相同时按ID从大到小排序（ID自增的情况下，更大的ID对应更新的记录）
• LEFT JOIN：保证即使没有支付记录的订单也能被查询出来

方法二：使用PostgreSQL专属的DISTINCT ON #

PostgreSQL的DISTINCT ON语法可以更简洁地实现需求，它会保留每个分组中排序最靠前的记录：

SELECT o.*, p.status AS latest_payment_status, p.date AS latest_payment_date
FROM orders o
LEFT JOIN (
    SELECT DISTINCT ON (order_id) *
    FROM payments
    -- 先按订单ID分组，再按日期、ID倒序排序，确保取每组最新的记录
    ORDER BY order_id, date DESC, id DESC
) p ON o.id = p.order_id;

• DISTINCT ON (order_id)：对每个订单ID只保留第一条符合排序规则的记录
• 排序规则必须以order_id开头，后续的date DESC, id DESC用来确定每组内的“最新”记录

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