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

本文介绍了查询仅显示每个线程的最新消息的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

环境 PHP 5.3.5 phpMyAdmin 3.3.9

Environment PHP 5.3.5 phpMyAdmin 3.3.9

这是 sqlfiddle 中问题的链接

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

Trying to create a query which filters through a user's message activity. The activity will display the most recent threads based upon message recently added. So I have to consider if the user is the recipient or sender.

我有一个结构化的查询，但在对线程进行分组时遇到了很大的困难.当我将线程分组时，最近活动的顺序丢失了.

I have a query structured but I am having great difficulty on grouping the threads. When I group the threads, the order of the most recent activity is lost.

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

Here is the query and results which captures the correct order of the last 10 messages but there are duplicate thread_ids. I want to only display the most recent thread based upon the message sent date.

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

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

Can this be done in one single query or should I create another query based upon the results of the first query?

感谢任何可以帮助我解决此查询的人....

Thank you for anyone that can help me solve this query....

推荐答案

您的各种数据集、查询和结果似乎并不相互对应，因此有点难以理解，但我怀疑您在追求某些东西沿着这些路线...

Your various data sets, queries, and results do not appear to correspod with one another so it's a little difficult to follow, but I suspect you're after something along these lines...

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;