如何从tableA获取全量记录并关联tableB的最新关联记录

Hey there! Let's figure out how to get every record from tableA paired up with the latest related entry from tableB—this is a super common SQL problem, so I’ve got a few solid solutions for you depending on what database you’re using.

方法1：窗口函数（适配多数现代SQL数据库） #

If you’re using MySQL 8.0+, PostgreSQL, SQL Server, or any database that supports window functions, this is the cleanest approach. We’ll assign a row number to each entry in tableB, grouped by the field that links it to tableA, ordered by your "latest" criteria (like a timestamp or auto-increment ID). Then we’ll only keep the row with the number 1 (the newest one) and join it to tableA.

Assuming your linking field is a_id in tableB, and create_time is the field that determines recency:

SELECT a.*, b.*
FROM tableA a
LEFT JOIN (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY a_id ORDER BY create_time DESC) AS row_num
    FROM tableB
) b ON a.id = b.a_id AND b.row_num = 1;

• PARTITION BY a_id groups tableB entries by their linked tableA ID
• ORDER BY create_time DESC makes the newest entry in each group get row_num = 1
• LEFT JOIN ensures all tableA records are kept, even if there’s no matching entry in tableB

方法2：子查询获取最新记录标识（适配旧版SQL） #

If you’re stuck with an older database (like MySQL 5.x) that doesn’t support window functions, this method works reliably. First, we’ll find the latest timestamp (or highest ID) for each a_id in tableB, then use that to fetch the full matching record.

用时间戳找最新记录： #

SELECT a.*, b.*
FROM tableA a
LEFT JOIN tableB b 
  ON a.id = b.a_id
  AND b.create_time = (
      SELECT MAX(create_time)
      FROM tableB
      WHERE a_id = a.id
  );

用自增ID找最新记录（避免同时间戳多条记录的问题）： #

If multiple tableB entries for the same a_id have the exact same timestamp, the above query might return duplicates. If your tableB has an auto-incrementing primary key (id), use this instead:

SELECT a.*, b.*
FROM tableA a
LEFT JOIN tableB b 
  ON a.id = b.a_id
  AND b.id = (
      SELECT MAX(id)
      FROM tableB
      WHERE a_id = a.id
  );

方法3：LATERAL JOIN/OUTER APPLY（适配PostgreSQL、SQL Server） #

This approach is more intuitive for some folks—it lets you directly fetch the latest tableB entry for each tableA record in a single step.

PostgreSQL版本： #

SELECT a.*, b.*
FROM tableA a
LEFT JOIN LATERAL (
    SELECT *
    FROM tableB
    WHERE a_id = a.id
    ORDER BY create_time DESC
    LIMIT 1
) b ON true;

SQL Server版本： #

SELECT a.*, b.*
FROM tableA a
OUTER APPLY (
    SELECT TOP 1 *
    FROM tableB
    WHERE a_id = a.id
    ORDER BY create_time DESC
) b;

关键注意点 #

• Always use LEFT JOIN (or OUTER APPLY) instead of INNER JOIN if you need to keep all tableA records, even those with no matching tableB entries.
• Make sure your "latest" sorting criteria is unambiguous—using a timestamp plus a unique ID is ideal to avoid ties.

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