你的原SQL语句核心问题是没有按单个订单维度去获取最新运输记录，而是全局查询了所有状态为'OK'的运输记录里的最大日期，这会导致你拿到的是所有在这个日期的运输记录，完全忽略了每个订单自身的最新运输状态。

针对你的需求，这里提供两种常用的解决方案：

方法一：使用窗口函数（推荐，逻辑清晰） #

利用ROW_NUMBER()窗口函数，按订单分组后给每条运输记录按日期倒序编号，编号为1的就是该订单的最新运输记录，再筛选这条记录的状态为'OK'即可：

SELECT ord.id AS order_id, tr.id AS transportation_id, tr.date, tr.status
FROM orders ord
JOIN (
    SELECT 
        order_id,
        id,
        date,
        status,
        -- 按订单分组，每组内按日期倒序排序，最新的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY date DESC) AS rn
    FROM transportation
) tr ON ord.id = tr.order_id
WHERE tr.rn = 1 AND tr.status = 'ok';

方法二：子查询获取每个订单的最新日期 #

先通过子查询得到每个订单的最新运输日期，再关联运输表找到对应日期的记录，最后筛选状态为'OK'的订单：

SELECT ord.id AS order_id, tr.id AS transportation_id, tr.date, tr.status
FROM orders ord
JOIN transportation tr ON ord.id = tr.order_id
-- 子查询得到每个订单的最新运输日期
JOIN (
    SELECT order_id, MAX(date) AS latest_date
    FROM transportation
    GROUP BY order_id
) latest_tr ON tr.order_id = latest_tr.order_id AND tr.date = latest_tr.latest_date
WHERE tr.status = 'ok';

验证示例数据 #

针对你提供的示例数据：

order_id, transportation_id, date, status
001, 001, 01/01/19, ok
001, 002, 01/01/20, ca
002, 003, 01/01/19, ca
002, 004, 01/01/18, ok
003, 005, 01/01/17, ok
003, 006, 01/01/16, ca

这两种方法都会准确筛选出003, 005, 01/01/17, ok这条符合要求的记录——因为订单003的最新运输记录状态确实是'OK'，而其他订单的最新记录状态都不是'OK'。

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