mysql查询两个表，为每个会话检索消息

I have two tables.

我有两个表。

One of them called "conversations" has the following data.

其中一个叫做“对话”的数据如下。

ConversationID  Sender     Reciever
1               bla1       bla2
2               bla1       bla3
3               bla1       bla4

The other is called "Messages" has the following data.

另一种叫做“消息”，有以下数据。

MessageID    MessageText    TimeAddedMessage        ConversationID
1             helo           2012-03-12 13:00:00          2
2             helo           2012-03-12 13:01:00          1
3             helo           2012-03-12 13:02:00          3
4             helo           2012-03-12 13:03:00          3
5             helo           2012-03-12 13:04:00          2

The result i want from the query is the following:

我想从查询中得到的结果如下:

5             helo           2012-03-12 13:04:00          2
4             helo           2012-03-12 13:03:00          3
2             helo           2012-03-12 13:01:00          1

Which means that we need the most recent comment for each conversation (sorted DESC by time).

这意味着我们需要每个对话的最新评论(按时间排序DESC)。

Any help appreciated.

任何帮助表示赞赏。

3 个解决方案

#1

Try -

试一试,

SELECT m2.*, c.Sender
FROM (
    SELECT m1.ConversationID, MAX(m1.MessageID) AS MessageID
    FROM Messages m1
    GROUP BY m1.ConversationID
) latest_msg
INNER JOIN Messages m2
    ON latest_msg.MessageID = m2.MessageID
    AND latest_msg.ConversationID = m2.ConversationID
INNER JOIN Conversations c
    ON m2.ConversationID = c.ConversationID
ORDER BY m2.MessageID DESC

EDIT I have modified the above query to include the value of Sender from the Conversations table. I have noticed that your structure for the conversation is a bit odd. A conversation is FROM one user TO another user but there is no way to identify which user wrote each message. Is this intentional?

我已经修改了上面的查询，以包含来自对话表的Sender的值。我注意到你谈话的结构有点奇怪。对话是从一个用户到另一个用户，但是无法确定每个消息是哪个用户写的。这是故意的吗?

#2

You can do it by creating derived table with ConversationID and max TimeAddedMessage and joining it back to Messages:

您可以通过使用ConversationID和max TimeAddedMessage创建派生表并将其连接到消息中来实现:

select Messages.MessageID, Messages.MessageText, 
       Messages.TimeAddedMessage, Messages.ConversationID
  from Messages inner join
  (
    select ConversationID, max (TimeAddedMessage) TimeAddedMessage
      from Messages
     group by ConversationID
  ) LastMessages
  on Messages.ConversationID = LastMessages.ConversationID 
     and Messages.TimeAddedMessage = LastMessages.TimeAddedMessage

#3

A simple query with good performance for comparitively small datasets.

一个简单的查询，具有比较小的数据集的良好性能。

SELECT m1.*
FROM Messages m1 LEFT JOIN Messages m2
 ON (m1.ConversationId = m2.ConversationId AND m1.TimeAddedMessage < m2.TimeAddedMessage)
WHERE m2.MessageID IS NULL;

Modified from this post.

从这篇文章修改。

It's kind of counter intuitive but has become somewhat of an often used cookbook recipe.

这是一种反直觉的做法，但已经成为了一种常用的食谱食谱。

#1