MYSQL Left Join结果的最后一行 [英] MYSQL Left Join last row of result

问题描述

我需要一些MYSQL帮助.

I am in need of some MYSQL help.

我有一个称为票证的表和一个名为statusLogs的表，我该如何:

I have a table called Tickets and a table called statusLogs how can I:

select 
  t.*,
  sl.statusId
from
  ticket LEFT JOIN (
    select * from statusLog where ticket_ticketId = t.ticketId order by statusLogId DESC LIMIT 1
  ) sl

基本上，我想在一个语句中为给定的票号选择最后一个statusLog.

Basically, I would like to select the last statusLog for a given ticket number in one statement.

推荐答案

尝试一下.它加入了statusLog的子选择，该子选择为每个ticket_ticketId拉最高的(因此我认为是最新的)statusLogId.检索票证的statusLogId.然后，另一个联接将statusId与位于第一个联接中的statusLogId匹配.

Try this. It joins a subselect of statusLog which pulls the highest (therefore most recent I think) statusLogId for each ticket_ticketId. That retrieves the statusLogId for the ticket. A further join then matches the statusId to the statusLogId located in the first join.

SELECT
  t.*,
  slid.statusId
FROM
  ticket t LEFT JOIN (
    SELECT ticket_ticketId, MAX(statusLogId) AS statusLogId FROM statusLog GROUP BY ticket_ticketId
  ) sl ON t.ticketId = sl.ticket_ticketId
  JOIN statusLog slid ON slid.statusLogId = sl.statusLogId

这篇关于MYSQL Left Join结果的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！