MySQL 5.7左连接无法访问主表字段，获取关联表最新date方案咨询

需求：从表b中找出与表a的id匹配的最新date字段，并将该字段加入查询结果集。
最初尝试使用LATERAL左连接实现，但MySQL 5.7不支持LATERAL语法，因此改用子查询写法：

最初的LATERAL连接SQL：

SELECT
    a.id,
    a.NAME,
    b.date
FROM
    a
LEFT JOIN LATERAL (
    SELECT * FROM b WHERE b.id = a.id ORDER BY date DESC LIMIT 1
    ) AS b
ON b.id = a.id 

改用的子查询SQL：

SELECT
    a.id,
    a.NAME,
    (   SELECT b.date FROM b WHERE a.id = b.id ORDER BY b.date DESC LIMIT 1
    ) AS date 
FROM
    a

咨询：该子查询方案是否可行？有无更优解决方式？

子查询方案的可行性 #

这个子查询方案完全可行：

• 逻辑上，它会为表a的每一行，在表b中找到对应id的最新date（通过ORDER BY date DESC LIMIT 1实现）；如果表b中没有匹配的id，会返回NULL，符合LEFT JOIN的预期效果。
• 语法上，MySQL 5.7支持这种关联子查询，可正常执行。

需要注意：如果表b数据量较大，且未给b.id和b.date建立联合索引，这个子查询会对表a的每一行都执行一次表b的排序查询，性能可能较差。建议创建联合索引idx_b_id_date (id, date DESC)来优化查询效率。

更优的替代方案 #

在MySQL 5.7中，可通过分组+关联的方式先获取每个id的最新date，再和表a、表b关联，这种方式通常比关联子查询性能更好（尤其是数据量大时）：

SELECT
    a.id,
    a.NAME,
    b_latest.date
FROM
    a
LEFT JOIN (
    -- 先获取每个id对应的最大date
    SELECT id, MAX(date) AS date
    FROM b
    GROUP BY id
) AS b_max ON a.id = b_max.id
-- 关联表b拿到对应记录（若同一id有多个相同最大date的记录，会返回多条，可按需调整）
LEFT JOIN b AS b_latest ON b_max.id = b_latest.id AND b_max.date = b_latest.date;

如果需要确保每个id仅返回一条最新记录（即使存在相同最大date的情况），可结合变量模拟窗口函数的效果：

SELECT
    a.id,
    a.NAME,
    b_latest.date
FROM
    a
LEFT JOIN (
    SELECT
        id,
        date,
        -- 用变量为每个id的记录按date降序排序
        @row_num := IF(@prev_id = id, @row_num + 1, 1) AS row_num,
        @prev_id := id
    FROM b, (SELECT @row_num := 0, @prev_id := NULL) AS vars
    ORDER BY id, date DESC
) AS b_latest ON a.id = b_latest.id AND b_latest.row_num = 1;

这种方式能精准获取每个id的第一条最新记录，同时避免关联子查询的多次查询开销，适合数据量较大的场景。

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