SQL一对多关联查询如何获取从表最新日期对应记录

核心需求为a表每一行匹配其关联b表中日期字段最新的对应b行全量字段，常见实现有三种，可根据使用的数据库版本选择：

方案1：窗口函数写法（通用推荐，适配主流数据库新版本） #

该写法逻辑清晰、兼容性好，是目前生产环境最常用的实现：

SELECT a.*, b.*
FROM a
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY foreign_key_to_a 
            ORDER BY b_date_field DESC -- 替换为b表存储日期的实际字段名
            -- 若同一日期可能对应多条b记录，可追加排序字段如 ORDER BY b_date_field DESC, id DESC 保证结果稳定
        ) AS rn
    FROM b
) b ON a.id = b.foreign_key_to_a AND b.rn = 1;

说明：如果同一个a关联的b表存在多条日期并列最新的记录，ROW_NUMBER()会随机取其中1条；如果需要把并列最新的记录全部返回，把ROW_NUMBER()替换成RANK()即可。如果不需要保留a表中无任何关联b记录的行，可以把LEFT JOIN换成INNER JOIN。

方案2：关联聚合子查询写法（兼容老版本数据库） #

如果使用的数据库不支持窗口函数（比如MySQL 5.x版本），可以用该写法：

SELECT a.*, b.*
FROM a
LEFT JOIN (
    SELECT foreign_key_to_a, MAX(b_date_field) AS max_date
    FROM b
    GROUP BY foreign_key_to_a
) b_latest ON a.id = b_latest.foreign_key_to_a
LEFT JOIN b ON b.foreign_key_to_a = a.id AND b.b_date_field = b_latest.max_date;

注意：该写法如果同一个a下有多条b记录的日期等于最大日期，会返回多行匹配结果，需要单条结果的话可以再加一层去重逻辑。

方案3：横向关联写法（高性能场景可选） #

如果使用的数据库支持LATERAL语法（PostgreSQL 9.3+、MySQL 8.0.14+）或者APPLY语法（SQL Server），在b表建了(foreign_key_to_a, b_date_field DESC)联合索引的场景下，该写法性能最优：

-- PostgreSQL、MySQL 适用
SELECT a.*, b.*
FROM a
LEFT JOIN LATERAL (
    SELECT * FROM b
    WHERE b.foreign_key_to_a = a.id
    ORDER BY b.b_date_field DESC
    LIMIT 1
) b ON true;

-- SQL Server 适用
SELECT a.*, b.*
FROM a
OUTER APPLY (
    SELECT TOP 1 * FROM b
    WHERE b.foreign_key_to_a = a.id
    ORDER BY b.b_date_field DESC
) b;

优化提示 #

• 尽量避免直接写a.*, b.*，如果两张表存在同名字段（比如id、create_time），建议显式指定字段并加别名，避免字段冲突。
• 不管使用哪种写法，给b表建(foreign_key_to_a, 日期字段 DESC)的联合索引，都能大幅提升查询速度。

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