MySQL关联查询：获取所有请求及对应最新位置报告（含无报告请求）

我有一个Requests表，需要查询其中所有数据；同时关联PositionReports表，仅获取每个请求对应的最新位置报告。
最初的SQL在只有一条位置报告时正常，但多条时无法获取最新的：

SELECT
r.id mission_id,
report.Timestamp as reportTimestamp,
report.Latitude as reportLat,
report.Longitude as reportLng,
FROM Requests r
LEFT JOIN PositionReports report ON r.id = report.RequestId

更新后，参考方案实现了获取最新报告，但只能检索到有位置报告的请求：

WHERE report.Id = (
    SELECT MAX(Id)
    FROM PositionReports
    WHERE RequestId = r.Id
);

希望同时返回无位置报告的请求，尝试外连接无效。数据库为MySQL（测试环境MariaDB，生产环境DigitalOcean MySQL），能否用单条SQL实现？

样例数据 #

Requests表 #

PositionReports表 #

期望输出 #

可以通过单条SQL实现，核心是把筛选最新报告的逻辑从WHERE子句移到LEFT JOIN的关联条件中，这样就不会过滤掉没有位置报告的请求。

方法一：基于Id筛选最新记录 #

SELECT
    r.id AS requestId,
    report.Timestamp,
    report.Latitude,
    report.Longitude
FROM Requests r
LEFT JOIN PositionReports report 
    ON r.id = report.RequestId
    AND report.Id = (
        SELECT MAX(Id)
        FROM PositionReports
        WHERE RequestId = r.Id
    );

方法二：优先按Timestamp筛选最新记录 #

如果需要优先按Timestamp降序取最新（Timestamp相同时再按Id降序），可以修改子查询：

SELECT
    r.id AS requestId,
    report.Timestamp,
    report.Latitude,
    report.Longitude
FROM Requests r
LEFT JOIN PositionReports report 
    ON r.id = report.RequestId
    AND (report.Timestamp, report.Id) = (
        SELECT MAX(Timestamp), MAX(Id)
        FROM PositionReports
        WHERE RequestId = r.Id
        GROUP BY RequestId
    );

方法三：窗口函数（MySQL 8.0+/MariaDB 10.2+） #

如果你的数据库版本支持窗口函数，用ROW_NUMBER()实现会更直观，可读性更强：

WITH RankedReports AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY RequestId 
            ORDER BY Timestamp DESC, Id DESC
        ) AS rn
    FROM PositionReports
)
SELECT
    r.id AS requestId,
    rr.Timestamp,
    rr.Latitude,
    rr.Longitude
FROM Requests r
LEFT JOIN RankedReports rr 
    ON r.id = rr.RequestId
    AND rr.rn = 1;

注意事项 #

• 方法一和方法二通过LEFT JOIN的关联条件筛选最新报告，不会排除没有对应报告的Requests记录；
• 窗口函数方法适合复杂的排序规则，代码逻辑更清晰，但需要确保数据库版本支持；
• 所有方法返回结果中，无位置报告的请求对应字段会显示NULL，与期望输出的空值效果一致。

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