mysql - 查询仅显示每个线程的最新消息

Question

环境 PHP 5.3.5 phpMyAdmin 3.3.9

Here's an link of the issue in sqlfiddle

尝试创建一个查询来过滤用户的消息事件。该事件将根据最近添加的消息显示最新的线程。所以我必须考虑用户是收件人还是发件人。

我有一个结构化查询，但在对线程进行分组时遇到很大困难。当我对线程进行分组时，最近事件的顺序会丢失。

这里是查询和结果，捕获了最后 10 条消息的正确顺序，但存在重复的 thread_ids。我只想根据消息发送日期显示最新的线程。

SQL查询:

SELECT m.date_sent, m.thread_id, m.message_id, m.sender_id,
  upub2.firstname as sender_name, mr.recipient_id,
  upub1.firstname as recipient_name
FROM message AS m
  JOIN message_recipient AS mr ON mr.message_id = m.message_id
  JOIN user_public_info AS upub1 ON upub1.user_public_info_id = mr.recipient_id
  join user_public_info AS upub2 ON upub2.user_public_info_id = m.sender_id
WHERE ((m.senderDelete IS NULL OR m.senderDelete = 0) AND m.sender_id = 2 ) OR
  ((mr.is_delete IS NULL OR mr.is_delete = 0 ) AND mr.recipient_id = 2)
ORDER BY m.message_id;

结果:

date_sent           thread_id   message_id  sender_id   sender_name     recipient_id    recipient_name
2013-10-09 14:31:50     106         113             1               John             2          Mark
2013-10-09 14:30:50     107         112             2           Mark             1          John
2013-10-09 14:30:31     106         111             2           Mark             1          John
2013-10-09 09:49:58     112         110             1           John             2          Mark
2013-10-09 09:20:24     108         106             1           John             2          Mark
2013-10-07 15:46:15     107         105             1           John             2          Mark
2013-10-07 14:40:25     103         104             1           John             2          Mark
2013-10-07 14:39:37     103         103             1           John             2          Mark
2013-10-07 14:36:34     107         102             2           Mark             1          John
2013-10-07 14:36:07     106         101             2           Mark             1          John
2013-10-07 14:35:29     105         100             2           Mark             1          John
2013-10-07 12:32:50     104         99          2           Mark             1          John
2013-10-07 12:15:43     104         98          2           Mark             1          John
2013-10-07 11:46:36     104         97          2       Mark             1          John
2013-10-07 11:43:32     104         96          1           John             2          Mark
2013-10-07 11:43:17     104         95          1           John             2          Mark
2013-10-07 11:27:14     103         94          1           John             2          Mark

我想要的是这样的:

date_sent           thread_id   message_id  sender_id   sender_name     recipient_id    recipient_name
2013-10-09 14:31:50     106         113             1       John             2          Mark
2013-10-09 14:30:50     107         112             2           Mark             1          John
2013-10-09 09:49:58     112         110             1           John             2          Mark
2013-10-09 09:20:24     108         106             1           John             2          Mark
2013-10-07 14:40:25     103         104             1           John             2          Mark
2013-10-07 14:35:29     105         100             2           Mark             1          John
2013-10-07 12:32:50     104         99          2           Mark             1          John

这可以在一个查询中完成吗？还是我应该根据第一个查询的结果创建另一个查询？

感谢任何可以帮助我解决此问题的人...

Answer 1

您的各种数据集、查询和结果似乎彼此并不对应，因此有点难以理解，但我怀疑您正在寻找类似的东西......

 SELECT m.message_id m_id
      , m.sender_id s_id
      , m.thread_id t_id
      , m.subject
      , m.message
      , m.date_sent
      , s.firstname sender
      , r.firstname recipient
   FROM message m
   JOIN message_recipient n
     ON n.message_id = m.message_id
   JOIN user_public_info s
     ON s.user_public_info_id = m.sender_id
   JOIN user_public_info r
     ON r.user_public_info_id = n.recipient_id
   JOIN (SELECT thread_id, MAX(max_message_id) max_message_id FROM message GROUP BY thread_id)x
     ON x.thread_id = m.thread_id AND x.max_message_id = m.message_id;