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

本文介绍了选择两个用户在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)