mysql - 在mysql中选择两个用户通信之间的最新消息

Question

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

     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 的用户，我需要这样的结果

    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

也就是说，我需要特定用户的最新消息，无论他是发送者还是接收者。

虽然这看起来很容易，但我找不到编写单个 mysql 查询的方法。

此外，如果对于这种解决方案，我的表格设计很差，我想提出建议。

注意:有一件事，我想我应该提到的是，例如用户 1 和用户 2 之间有多次通信。我需要他们两个之间的最新通信，无论用户 1 是发送者还是接收者，只有最近的。

大多数用户都建议查询，这将带来一条记录，发送者 1、接收者 2，然后是发送者 2、接收者 1。我不需要这两条记录，因为这是同一用户 1 和用户 2 之间的通信.我需要一个最新的一个给每个其他用户指定的用户。

谢谢，

Answer 1

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)，查询会简单得多:

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)