选择两个用户在mysql中的通讯之间的最新消息 [英] Select the Latest Message Between the communication of two user in mysql

问题描述

我有一个简单的表，用于维护用户之间的消息.表格结构看起来像

I have a simple table maintaining messages between users. The table structure looks like

     sender    receiver   message   sendtime
      1          2          m1      2012-01-01 12:12:12  
      2          1          m2      2012-01-01 12:50:20
      1          2          m3      2012-01-01 12:55:55
      1          3          m4      2012-01-02 05:05:05
      1          4          m5      2012-01-05 05:20:20
      4          1          m6      2012-01-06 06:05:00
      4          1          m7      2012-01-07 11:11:11
      2          4          m8      2012-01-08 05:01:01

现在，对于ID为1的用户，我需要这样的结果

Now, for the user with ID 1, I need the result like this

    sender     receiver   message   sendtime
     1           2          m3      2012-01-01 12:55:55
     1           3          m4      2012-01-02 05:05:05
     4           1          m7      2012-01-07 11:11:11

那是我需要给特定用户的最新消息，无论他是发送者还是接收者.

That is I need the recent messages for the particular user whether he is sender or receiver.

尽管看起来很简单，但我找不到写单个mysql查询的方法.

Though, it seem easy, I cannot find a way to write a single mysql query.

此外，我想建议一下，对于这种解决方案，我的表设计是否不好.

Also, I want suggestion if for this kind of solution, my table design is poor.

注意:我想提到的一件事是，例如，用户1和用户2之间存在多种通信.无论用户1是发送者还是接收者，我都需要两者之间的最新通信，而仅是最近的.

Note: One thing, which I think I should mention is, for instance there are multiple communication between user 1 and user 2. I need the latest communication between them two whether user 1 is a sender or receiver, only the recent.

大多数用户建议查询，该查询将与发送方1，接收方2和发送方2，接收方1一起记录一条记录.我不需要这两条记录，因为这是同一用户1和用户2之间的通信. .对于与其他每个用户指定的用户，我需要一个最新的.

Most users are suggesting the query, that will bring one record with the sender 1, receiver 2 and then sender 2, receiver 1. I don't need these two record because this is a communication between same user 1 and user 2. I need one the latest one for the user specified with each of other users.

谢谢

推荐答案

SELECT data.* FROM 
(SELECT MAX(sendtime) AS sendtime 
         FROM data 
         WHERE 1 IN (sender,receiver)
         GROUP BY IF (1 = sender,receiver,sender)) AS latest
LEFT JOIN data ON latest.sendtime = data.sendtime AND 1 IN (data.sender, data.receiver)
GROUP BY IF (1 = data.sender,data.receiver,data.sender)

我建议在表中使用唯一的序列ID，以避免使用外部GROUP BY. 如果您具有递增的唯一message_id(即更大的message_id对应于后来的sendtime)，则查询会简单得多:

I recommend to use unique sequence id in your table to avoid external GROUP BY. If you have incrementing unique message_id (i.e. bigger message_id corresponds to later sendtime), the query would be much simpler:

SELECT data.* FROM 
(SELECT MAX(message_id) AS message_id 
         FROM data 
         WHERE 1 IN (sender,receiver)
         GROUP BY IF (1 = sender,receiver,sender)) AS latest
LEFT JOIN data USING(message_id)

这篇关于选择两个用户在mysql中的通讯之间的最新消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！