mysql - 如何在mysql中使用group by和join？

Question

这是我的 mysql 查询:

SELECT t.id,
       t.user_id_1,
       t.user_id_2,
       m.id as message_id,
       m.from_user_id,
       m.text,
       max(m.date_sent),
       m.viewed
FROM   thread t
       JOIN message m 
            ON t.id = m.thread_id
WHERE t.user_id_1 = 1 
      OR t.user_id_2 = 1
GROUP BY t.id

有两个表，线程和消息。线程表存储记录(每条记录是一个对话)，消息表存储消息。它将有一个线程表的外键来指示消息所在的线程。

我正在尝试获取线程列表以及每个线程的最新消息。但上面的方法不起作用。

我想做的是，它获取所有线程，并将其与消息(在该线程中)连接起来。然后按线程 ID 对消息进行分组，并使用 max 函数给出的最新日期的记录。

但它会给出一些带有最新日期的随机消息(该消息来自不同的记录)。

有人知道如何解决这个问题吗？

Answer 1

您可以有一个单独的子查询，为表 message 上的每个 thread_id 获取最新的 date_sent。由于子查询只有两列，因此您需要连接表本身的结果以获取其他结果，前提是它在两列上都匹配:thread_id 和 date_sent。

SELECT  t.id, 
        t.user_id_1, 
        t.user_id_2, 
        m.id as message_id, 
        m.from_user_id, 
        m.text, 
        m.date_sent, 
        m.viewed
FROM    thread t
        INNER JOIN message m 
            ON t.id = m.thread_id
        INNER JOIN
        (
            SELECT  thread_id, MAX(date_sent) date_sent
            FROM    message
            GROUP   BY thread_id
        ) s ON  m.thread_id = s.thread_id
                AND m.date_sent = s.date_sent
WHERE   1 IN (t.user_id_1, t.user_id_2)