mysql - 如何从一个表中获取所有行，但过滤第二个表？

Question

我在 PHP 页面上运行一个查询，该查询将从一个表中提取所有记录，与其他两个表进行 INNER JOIN，然后列出所有结果。但是在第二张表上我只想要最近的记录。

这是我的问题

SELECT * FROM wn_trailer 
INNER JOIN (
    SELECT id, trailer_id, trailer_status, trailer_assigned, MAX(last_update), trailer_lat, trailer_long
    FROM wn_trailer_history
) AS th ON wn_trailer.id = th.trailer_id 
INNER JOIN wn_trailer_status ON wn_trailer_status.id = th.trailer_status 
INNER JOIN wn_users ON wn_users.id = th.trailer_assigned 
ORDER BY trailer_number ASC

查询运行但仅返回第一条记录。

Answer 1

您需要一个额外的 JOIN 来引入上次更新日期的数据。此外，您的子查询需要一个 GROUP BY:

SELECT *
FROM wn_trailer t INNER JOIN
     (SELECT trailer_id, MAX(last_update) as max_last_update
      FROM wn_trailer_history
      GROUP BY trailer_id
     ) tht
     ON t.id = tht.trailer_id INNER JOIN
     wn_trailer_history th
     ON th.trailer_id = tht.trailer_id AND
        th.last_update = tht.max_last_update INNER JOIN
     wn_trailer_status ts
     ON ts.id = th.trailer_status INNER JOIN
     wn_users u
     ON u.id = th.trailer_assigned 
ORDER BY trailer_number ASC;

我还添加了表别名，以便查询更易于编写和阅读。