mysql - 在左连接查询中获取每个时间戳的最新记录

Question

我目前有 4 个表，我查询这些表以列出订单状态。具有相关字段的表如下所示

+--------------+ +------------+ +----------+ +----------+
|Orders        | | Customers  | | Users    | | Status   |
+--------------+ +------------+ +----------+ +----------+
| id           | | id         | | id       | | id       |
| customer_id  | | name       | | name     | | order_id | 
| rep_id       | +------------+ +----------+ | status   |
+--------------+                             | comments |
                                             | date     |Timestamp
                                             | tech_id  |
                                             +----------+

使用以下 SQL，我可以显示订单列表和状态。

SELECT
      Orders.id AS orderid,
      Customers.name AS CLIENT,
      Users.name AS rep,
      Status.status
    FROM
      Orders
    LEFT JOIN
      Customers ON Orders.customer_id = Customers.id
    LEFT JOIN
      Users ON Orders.rep_id = Users.id
    LEFT JOIN
      Status ON Orders.id = Status.order_id

我得到了这样的东西。

+---------+----------+-------+--------+
| orderid | CLIENT   | rep   | status |
+---------+----------+-------+--------+
|       1 | Client 1 | Rep 1 | 1      |
|       2 | Client 2 | Rep 2 | 1      |
|       3 | Client 3 | Rep 1 | 1      |
|       4 | Client 4 | Rep 2 | 1      |
|       6 | Client 6 | Rep 4 | 1      |
|       1 | Client 1 | Rep 3 | 4      |
|       6 | Client 6 | Rep 4 | 4      |
+---------+----------+-------+--------+

我需要根据 Status 表上的最新日期为每个 orderid 获取一条记录。我觉得我很接近但无法弄清楚。

Answer 1

最简单的方法可能是相关子查询:

SELECT o.id AS orderid, c.name AS CLIENT, u.name AS rep,
       (SELECT s.status
        FROM status s
        WHERE o.id = s.order_id
        ORDER BY s.date DESC
        LIMIT 1
       ) latest_status
FROM Orders o LEFT JOIN
     Customers c
     ON o.customer_id = c.id LEFT JOIN
     Users u
     ON o.rep_id = u.id ;