在mysql中，按用户分组从表中查找最新的消息。
在mysql中，按用户分组从表中查找最新的消息。
我网站上有一个私信表，一段时间以来，我一直有一个收件箱和发件箱分开。我想要合并收件箱/发件箱，只显示与特定用户之间的最新消息。
示例表格
| id | fromuser | fromid | touser | toid | message | timestamp | -------------------------------------------------------------------------------- | 1 | user1 | 1 | user2 | 2 | 你好... | 2015-01-01 00:00:00 | | 2 | user1 | 1 | user3 | 3 | 好的... | 2015-01-02 00:00:00 | | 3 | user3 | 3 | user1 | 1 | 不是.... | 2015-01-03 00:00:00 | | 4 | user2 | 2 | user3 | 3 | 新的.... | 2015-01-04 00:00:00 | | 5 | user2 | 2 | user1 | 1 | 附上..... | 2015-01-05 00:00:00 | --------------------------------------------------------------------------------
我想要的结果是当用户为1时
| id | fromuser | fromid | touser | toid | message | timestamp | -------------------------------------------------------------------------------- | 3 | user3 | 3 | user1 | 1 | 不是.... | 2015-01-03 00:00:00 | | 5 | user2 | 2 | user1 | 1 | 附上..... | 2015-01-05 00:00:00 | --------------------------------------------------------------------------------
使用下面的代码，我可以让它显示与每个用户之间的一条消息，但它显示的是最旧的消息而不是最新的。
mysql_query("SELECT m1.*, users.id AS userid, users.username
FROM pm AS m1, pm AS m2, users
WHERE ((m1.fromuser='".$_SESSION['userName']."' AND users.id=m1.toid)
OR (m1.touser='".$_SESSION['userName']."' AND users.id=m1.fromid))
AND m2.id=m1.id ORDER BY timestamp DESC");
问题的出现原因：
- 第一段代码中的查询语句使用了not exists子查询来查找最新的消息，按用户分组。然而，该查询存在一些问题，导致返回了多个相同发送者的消息，而不是每个用户的最新消息。
解决方法：
- 第一段代码中的查询语句可以改进。第二段代码中的查询语句使用了left join和is null来找到每个用户的最新消息。
以下是改进后的查询语句：
select m.*
from messages m
left join messages m2
on ((m.fromuser = m2.fromuser and m.touser = m2.touser)
or (m.fromuser = m2.touser and m.touser = m2.fromuser))
and m.timestamp < m2.timestamp
where (m.fromuser = 'user1' or m.touser = 'user1')
and m2.id is null;
这个查询语句通过left join将消息表与自身连接，根据时间戳比较找到每个用户的最新消息。