MySQL基于DATETIME列LEFT JOIN关联表最新记录的实现问题

我懂你碰到的这个坑了——你之前的子查询只取了MAX(submitted)和分组后的id，但MySQL在分组时如果没把id放进聚合函数，它会随机选一个组内的id返回，这就导致job_id和你想要的最新submitted日期不匹配了。下面给你两种实用的解决办法，适配不同版本的MySQL：

这是最简洁且不易出错的方案，利用ROW_NUMBER()窗口函数给每个订单分组内的记录按submitted降序编号，取编号为1的就是最新那条：

SELECT 
    o.id AS order_id,
    j.submitted,
    j.id AS job_id
FROM orders o
LEFT JOIN (
    SELECT 
        id,
        order_id,
        submitted,
        -- 按订单分组，提交日期倒序排，每组第一条标记为1
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY submitted DESC) AS rn
    FROM jobs  -- 替换成你的关联表实际名称
) j ON o.id = j.order_id AND j.rn = 1;

这个方法能确保job_id和对应的最新submitted完全匹配，就算同一订单下有多个记录也不会出错。

如果你的MySQL版本不支持窗口函数，可以用两次关联的方式：先找到每个订单的最新提交日期，再关联回原表找到对应记录：

SELECT 
    o.id AS order_id,
    j.submitted,
    j.id AS job_id
FROM orders o
LEFT JOIN (
    -- 第一步：获取每个订单的最新提交日期
    SELECT order_id, MAX(submitted) AS latest_submitted
    FROM jobs
    GROUP BY order_id
) j_latest ON o.id = j_latest.order_id
-- 第二步：关联回jobs表，匹配订单ID和最新提交日期
LEFT JOIN jobs j ON o.id = j.order_id AND j.submitted = j_latest.latest_submitted;

⚠️ 注意：如果同一订单下有两条记录的submitted完全相同（比如同一时间提交），这个方法会返回多条记录。如果要避免这种情况，可以用NOT EXISTS子句进一步筛选：

SELECT 
    o.id AS order_id,
    j.submitted,
    j.id AS job_id
FROM orders o
LEFT JOIN (
    SELECT 
        j1.id,
        j1.order_id,
        j1.submitted
    FROM jobs j1
    -- 确保没有其他同订单的记录比当前记录更新（或同时间但ID更大）
    WHERE NOT EXISTS (
        SELECT 1 
        FROM jobs j2 
        WHERE j2.order_id = j1.order_id 
        AND (j2.submitted > j1.submitted OR (j2.submitted = j1.submitted AND j2.id > j1.id))
    )
) j ON o.id = j.order_id;

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