MySQL 5.6查询：获取未关联table2的记录及最新拒绝记录

背景说明 #

现有两张关联表 table1 和 table2，其中 table1.id 与 table2.lead_id 为关联字段。两张表的结构及数据如下：

table1 结构与数据 #

table2 结构与数据 #

需求目标 #

编写 MySQL 查询语句，输出以下内容：

• table1 中无对应 table2 记录的 loan_id（即 table1.id 未出现在 table2.lead_id 中的条目）
• table2 中每个 lead_id 对应的最新一条 REJECTED 记录的 loan_id（关联 table1 获取）和 created 时间

预期输出 #

注：预期结果中的 104 未在原 table1 数据中出现，推测为输入笔误，实际执行以下语句将返回 table1 所有 loan_id，无对应 table2 记录的 created 字段为 null。

实现语句 #

方法一：子查询分组获取最新记录（兼容低版本 MySQL） #

SELECT
    t1.loan_id,
    t2_latest.created
FROM table1 t1
LEFT JOIN (
    SELECT
        lead_id,
        created
    FROM table2
    WHERE lead_status = 'REJECTED'
    AND (lead_id, created) IN (
        SELECT lead_id, MAX(created)
        FROM table2
        WHERE lead_status = 'REJECTED'
        GROUP BY lead_id
    )
) t2_latest ON t1.id = t2_latest.lead_id;

方法二：窗口函数（MySQL 8.0+ 推荐） #

利用 ROW_NUMBER() 窗口函数直接标记每个 lead_id 的最新记录，写法更简洁高效：

SELECT
    t1.loan_id,
    t2_filtered.created
FROM table1 t1
LEFT JOIN (
    SELECT
        lead_id,
        created,
        ROW_NUMBER() OVER (PARTITION BY lead_id ORDER BY created DESC) AS rn
    FROM table2
    WHERE lead_status = 'REJECTED'
) t2_filtered ON t1.id = t2_filtered.lead_id AND t2_filtered.rn = 1;

内容的提问来源于stack exchange，提问作者Souvik Kumar Paul