按车辆ID分组获取最新上下线状态历史记录的SQL问题

你的核心需求是按车辆ID（vid）分组，获取每辆车的最新状态历史记录，但当前SQL存在逻辑问题：子查询同时取MAX(date_offline)和MAX(date_online)，这两个最大值大概率来自不同的历史记录，导致关联后无法匹配到正确的单条最新记录，甚至直接无结果。

正确SQL写法（使用窗口函数） #

SELECT h.id, h.vid, h.accnt_id, h.date_online, h.date_offline, v.platenumber
FROM (
    SELECT 
        id, vid, accnt_id, date_online, date_offline,
        -- 按vid分组，取最新状态记录：用COALESCE优先取date_offline（若存在），否则取date_online，倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY vid 
            ORDER BY COALESCE(date_offline, date_online) DESC
        ) AS rn
    FROM tbl_status_history
    WHERE accnt_id = 1 -- 提前过滤目标账户，提升查询效率
) h
JOIN tbl_vehicles v ON h.vid = v.vid AND h.accnt_id = v.accnt_id
WHERE h.rn = 1;

逻辑说明 #

1. 窗口函数分组排序：内层子查询用ROW_NUMBER()按vid分组，以「最新状态时间」（车辆下线则取date_offline，在线则取date_online）倒序排序，给每辆车的历史记录标记序号rn，最新记录的序号为1。
2. 过滤最新记录：外层查询只保留rn=1的记录，即每辆车的最新状态。
3. 关联车辆表：关联tbl_vehicles获取车牌号，同时加上accnt_id匹配，确保数据归属正确。

为什么原代码失效？ #

举个例子：某辆车有两条记录

• 记录1：2023-03-01上线，2023-03-02下线
• 记录2：2023-03-03上线，date_offline为null

原查询子查询会得到MAX(date_offline)=2023-03-02、MAX(date_online)=2023-03-03，这两个值分属不同记录，关联时找不到同时满足这两个条件的行，自然拿不到正确结果。

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