如何在不指定记录的情况下获取具体记录的最新日期？

Question

我有两个表:订单和运输。

ORDER  
id

TRANSPORTATION  
id  
order_id  
date  
status            (could be 'ok', 'ca', 'ko')

1个订单可以有多个运输。我想要所有最新运输状态为'OK'的订单。

如果我这样做了：

select ord.*
from   orders ord
       join transportation tr
       on ord.id = tr.order_id
where  tr.date = (select max(date) from transportation where status like 'OK');

我将获得所有运输的最新日期，但我只想要该订单中所有运输的最新日期。

例如，如果我有这些订单和这些运输，并且我想要每个订单的最后一个运输，其状态是'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

Answer 1

使用not exists执行此操作

select ord.* from orders ord join transportation tr on ord.id = tr.order_id
where tr.status = 'OK'
and not exists(select 1 from transportation b where b.status = 'OK' and tr.date > b.date)