SQL查询加入两个表与分组

I have two tables:

我有两张桌子:

ticket_entries:

ticket_id entry_date  status responsible_agent
1 1516284485 open 1
2 1516284485 open 1
3 1516284485 open 1
4 1516284485 open 1

ticket_logs

ticket_id entry_date responsible agent 
1 1516284486 1 
1 1516284487 1
1 1516284488 1
1 1516284489 1
1 1516284490 1
2 1516284485 1 
2 1516284482 1
etc

I'm trying to join the tables and get the result:

我正在尝试加入表格并获得结果:

ticket_id entry_date  status responsible_agent
1 1516284490 open 1
2 1516284485 open 1
3 1516284485 open 1
4 1516284485 open 1

with the entry_date from the ticket_logs showing the latest record for this ticket_id.

使用来自ticket_logs的entry_date显示此ticket_id的最新记录。

I tried to work with the ORDER BY and GROUP BY but then I got the first record from the ticket_logs instead od the latest:

我尝试使用ORDER BY和GROUP BY,但后来我从ticket_logs获得了第一条记录,而不是最新的:

      SELECT * FROM ticket_entries 
      JOIN ticket_logs 
           ON ticket_entries.ticket_id = ticket_logs.ticket_id 
     WHERE responsible_agent = '1' 
     GROUP BY ticket_entries.ticket_id 
     ORDER BY ticket_logs.entry_date DESC

3 个解决方案

#1

You can made the internal select at first which group the ticket_logs with max log and then join it with ticket_entries.

您可以首先进行内部选择,将ticket_logs与max log分组,然后将其与ticket_entries连接。

SELECT  
    ticket_entries.ticket_id,
    ticket_entries.status,
    selectMaxEntryDate.max_date,
    ticket_entries.responsible_agent
FROM
    (
    SELECT ticket_logs.ticket_id as ticket_id,max(ticket_logs.entry_date) as max_date
    FROM  ticket_logs
    WHERE ticket_logs.responsible_agent = '1' 
    GROUP BY ticket_logs.ticket_id
    ) as selectMaxEntryDate
    JOIN 
    ticket_entries  ON ticket_entries.ticket_id = selectMaxEntryDate.ticket_id 
ORDER BY 
    selectMaxEntryDate.max_date;

#2

SELECT
    e.ticket_id,
    MAX(l.entry_date) AS latest
    e.status,
    e.responsible_agent
FROM ticket_entries e
    INNER JOIN ticket_logs l
    ON e.ticket_id = l.ticket_id
WHERE e.responsible_agent = '1'
GROUP BY e.ticket_id, e.status, e.responsible_agent
ORDER BY MAX(l.entry_date) DESC

#3

   Your question is a bit confusing as your data doesn't entirely make sense, but it seems like you're looking for a simple Group By.  You can't do a Select * and then just Group on one field.

    select te.ticket_id, max(ts.entry_date), te.status, te.responsible_agent
    from ticket_entries as te
    inner join ticket_logs as ts
    on te.ticket_id = ts.ticket_id
    group by te.ticket_id, ts.status, te.responsible_agent;