mysql - 从订单中选择最后的订单状态

Question

我有 table :

订单:

id_order    id_customer    
1           1              
2           2              
3           1              

订单历史记录

id_history  id_order    id_order_state    date_add
1           1           1                 2010-01-01 00:00:00
2           1           2                 2010-01-02 00:00:00
3           1           3                 2010-01-03 00:00:00
4           2           2                 2010-05-01 00:00:00
5           2           3                 2011-05-02 00:00:00
6           3           1                 2011-05-03 00:00:00
7           3           2                 2011-06-01 00:00:00

订单状态

id_order_state    name
1                 New
2                 Sent
3                 Rejected
4                 ...

如何获取该订单的最后一个 id_order_state(最后我的意思是 MAX(id_history) 或 MAX(date_add))不等于 1 或 3 的所有 order_id？

Answer 1

select oh.id_history, oh.id_order, oh.id_order_state, oh.date_add
from (
    select id_order, max(date_add) as MaxDate
    from orders_history
    where id_order_state not in (1, 3)
    group by id_order 
) ohm
inner join orders_history oh on ohm.id_order = oh.id_order 
    and ohm.MaxDate = oh.date_add