寻求MySQL左连接查询方案：获取关联表对应最大b_id记录

问题场景 #

使用LEFT JOIN关联表A与表B（关联条件A.a_id = B.p_id），表B中存在多条p_id相同的记录，需仅保留表B中对应p_id下最大b_id的记录。

原查询的问题 #

你尝试的查询语句存在两个核心问题：

1. GROUP BY p_id后使用SELECT *，MySQL会返回分组内的随机行，无法保证拿到max(b_id)对应的完整记录。
2. HAVING max(b_id)无实际筛选作用，HAVING需配合比较逻辑才能生效，且即便调整也无法解决第一条问题。

正确的查询方法 #

方法一：子查询先获取每个p_id的最大b_id，再关联 #

先通过子查询找出表B中每个p_id对应的最大b_id，再关联表A和表B拿到完整数据：

SELECT A.a_id, B.b_id, A.column1, B.column2
FROM A
LEFT JOIN (
    SELECT p_id, MAX(b_id) AS max_b_id
    FROM B
    GROUP BY p_id
) AS B_max ON A.a_id = B_max.p_id
LEFT JOIN B ON B_max.p_id = B.p_id AND B_max.max_b_id = B.b_id;

方法二：使用窗口函数ROW_NUMBER()（MySQL 8.0+支持） #

利用窗口函数给每个p_id分组内的记录按b_id降序排序，取排序为1的记录（即最大b_id的那条）：

SELECT a_id, b_id, column1, column2
FROM (
    SELECT 
        A.a_id, 
        B.b_id, 
        A.column1, 
        B.column2,
        ROW_NUMBER() OVER (PARTITION BY A.a_id ORDER BY B.b_id DESC) AS rn
    FROM A
    LEFT JOIN B ON A.a_id = B.p_id
) AS temp
WHERE rn = 1;

执行结果 #

两种方法均可得到预期结果：

a_id b_id   column1 column2
 1   2      Adam    Hash 
 2   3      Voge    kyu

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