如何从内连接（inner join）关联的右表获取最新记录并返回单条左表数据

方案1：窗口函数法（适用所有支持窗口函数的主流数据库：MySQL8.0+、PostgreSQL、Oracle、SQL Server等） #

先对右表按关联键分组，组内按sequence number倒序排序，取每组排名第一的记录即为最新数据，再和左表关联即可避免重复。
示例代码（假设左表为交易主表trade_order，右表为交易明细表trade_detail，关联字段为order_id，右表序列号字段为seq_no）：

SELECT t1.*, t2.*
FROM trade_order t1
INNER JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY seq_no DESC) AS rn
    FROM trade_detail
) t2 ON t1.order_id = t2.order_id AND t2.rn = 1;

若相同最大序列号存在多条记录需要全部返回，将ROW_NUMBER()替换为RANK()即可。

方案2：子查询匹配最大值法（兼容所有数据库，包括低版本MySQL、SQLite等不支持窗口函数的场景） #

先查询每个关联键对应的最大序列号，再关联右表取出对应最新记录，最后和左表关联。
示例代码：

SELECT t1.*, t2.*
FROM trade_order t1
INNER JOIN (
    SELECT td.*
    FROM trade_detail td
    INNER JOIN (
        SELECT order_id, MAX(seq_no) AS max_seq
        FROM trade_detail
        GROUP BY order_id
    ) max_seq_tab ON td.order_id = max_seq_tab.order_id AND td.seq_no = max_seq_tab.max_seq
) t2 ON t1.order_id = t2.order_id;

方案3：横向关联法（适用PostgreSQL、SQL Server、Oracle 12c+，查询性能更优） #

通过横向关联逐行匹配左表关联键对应的右表最新记录，避免全表扫描右表。
示例代码：

SELECT t1.*, t2.*
FROM trade_order t1
CROSS JOIN LATERAL (
    SELECT *
    FROM trade_detail td
    WHERE td.order_id = t1.order_id
    ORDER BY seq_no DESC
    LIMIT 1
) t2;

注意事项 #

• 若需要保留无匹配右表记录的左表数据，将对应INNER JOIN替换为LEFT JOIN即可
• 建议提前给右表的关联字段、序列号字段建立联合索引，可大幅提升查询效率

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