mysql - 从表中获取所有行 - 使用另一个表中的最新行，以及基于最新行的另一个表

Question

我需要从订单表中获取所有详细信息，包括订单状态表中的最新状态 ID，然后是状态表中该状态的名称。

订单

id | customer | product
-----------------------
1  | David    | Cardboard Box

Order_to_statuses

id | order | status | updated_at
--------------------------------
1  | 1     | 1      | 2017-05-30 00:00:00
2  | 1     | 3      | 2017-05-28 00:00:00
3  | 1     | 4      | 2017-05-29 00:00:00
4  | 1     | 2      | 2017-05-26 00:00:00
5  | 1     | 5      | 2017-05-05 00:00:00

order_states

id | name 
---------
1  | Pending
2  | Paid
3  | Shipped
4  | Refunded

在这种情况下，我需要获取 customer 和 product，以及订单状态表中的最新状态 ID，然后是该状态的名称。

我该怎么做？

Answer 1

我会首先获取每个 order 的 max(updated_at) 来分解它，然后处理您需要的所有其他内容。您可以使用子查询获取每个订单的最大日期:

select  
   s.`order`, 
   s.`status`,
   s.updated_at
from order_to_statuses s
inner join 
(
   select 
       `order`,
       max(updated_at) as updated_at
   from order_to_statuses
   group by `order`
) m
   on s.`order` = m.`order`
   and s.updated_at = m.updated_at

一旦你得到这个，你现在就有了订单、状态 ID 和最近的日期。然后你可以使用它连接到其他表，进行完整查询:

select 
    o.customer,
    o.product,
    ots.updated_at,
    os.name
from orders o
inner join
(
   select  
       s.`order`, 
       s.`status`,
       s.updated_at
   from order_to_statuses s
   inner join 
   (
       select 
           `order`,
           max(updated_at) as updated_at
       from order_to_statuses
       group by `order`
   ) m
       on s.`order` = m.`order`
       and s.updated_at = m.updated_at
) ots
    on o.Id = ots.`order`
inner join order_states os
    on ots.`status` = os.id;

查看 demo