MySQL多表关联查询MAX字段时无法获取对应EngineerID的解决方法

嗨，你遇到的这个问题确实是SQL里经典的greatest-n-per-group场景。你的初始查询之所以拿不到正确的EngineerID，是因为当你按s.idsites分组时，数据库只会保证MAX(sd.scheduleddate)是该组的最大值，但sd.EngineerID并没有和这个最大值绑定——它会返回该组中任意一条记录的工程师ID（不同数据库的处理逻辑略有不同，但都不是你要的对应值）。

下面给你两种实用的解决方案，适配不同的数据库环境：

方法一：使用窗口函数（推荐，适配现代数据库） #

窗口函数可以给每个地址的出勤记录按日期排序，精准定位到最新的那条记录，从而拿到对应的工程师ID：

WITH ranked_schedules AS (
    SELECT 
        s.sitename,
        sd.scheduleddate,
        sd.EngineerID,
        -- 按地址分组，按出勤日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY s.idsites ORDER BY sd.scheduleddate DESC) AS rn
    FROM sites s
    LEFT JOIN scheduled_dates sd ON sd.idsites = s.idsites
)
SELECT sitename, scheduleddate, EngineerID
FROM ranked_schedules
WHERE rn = 1;

说明： #

• PARTITION BY s.idsites：确保每个地址的出勤记录单独分组排序
• ORDER BY sd.scheduleddate DESC：让最新的出勤日期排在每组的最前面
• ROW_NUMBER()：给每组内的记录按排名编号，最后筛选rn=1的记录，就是每个地址的最新出勤信息

如果同一个地址在同一天有多个工程师出勤，ROW_NUMBER()会随机保留一条；如果需要显示所有当天的记录，可以把ROW_NUMBER()换成RANK()。

方法二：子查询关联（适配老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.7及更早版本），可以先查询每个地址的最新出勤日期，再关联回原表匹配对应的工程师ID：

SELECT 
    s.sitename,
    sd.scheduleddate,
    sd.EngineerID
FROM sites s
LEFT JOIN scheduled_dates sd ON sd.idsites = s.idsites
INNER JOIN (
    -- 先获取每个地址的最新出勤日期
    SELECT idsites, MAX(scheduleddate) AS latest_date
    FROM scheduled_dates
    GROUP BY idsites
) latest ON sd.idsites = latest.idsites AND sd.scheduleddate = latest.latest_date;

说明： #

• 子查询latest先计算出每个地址的最大出勤日期
• 再将scheduled_dates表与这个子查询关联，同时匹配地址ID和日期，这样就能精准定位到对应最新日期的工程师ID

两种方法都保留了你初始查询的LEFT JOIN逻辑，所以没有出勤记录的地址也会被保留，对应的scheduleddate和EngineerID会显示为NULL。

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