我有一个mysql查询,我在排序发件人的最新消息时遇到问题
这是我的查询如下:
SELECT
`Mes`.`fromid`,
`Mes`.`is_read`,
`Mes`.`id` AS `mesid`,
`Mes`.`message`,
max(Mes.date) AS `date`,
`User`.`username`,
`User`.`MemberID` AS `Uid`
FROM `messages` AS `Mes`
INNER JOIN `users` AS `User` ON User.MemberID = Mes.fromid
WHERE (Mes.toid = 5 AND Mes.fromid <> 5)
GROUP BY `Mes`.`fromid`
ORDER BY `date` DESC
这是我的数据库表:
用户表
MemberID UserName Email Password
1 User1 user1@gmail.com 123456
2 User2 user2@gmail.com 123456
3 User3 user3@gmail.com 123456
4 User4 user4@gmail.com 123456
5 User5 user5@gmail.com 123456
消息表:
id fromid toid message is_read date
1 5 2 hello test 1 1 2012-08-24 01:00:00
2 2 5 hello test 2 1 2012-08-24 02:00:00
3 3 5 hello test 3 1 2012-08-24 03:00:00
4 4 5 hello test 4 1 2012-08-24 04:00:00
5 2 5 hello test 5 1 2012-08-25 05:00:00
和我的查询的输出:
SELECT
`Mes`.`fromid`,
`Mes`.`is_read`,
`Mes`.`id` AS `mesid`,
`Mes`.`message`,
max(Mes.date) AS `date`,
`User`.`username`,
`User`.`MemberID` AS `Uid`
FROM `messages` AS `Mes`
INNER JOIN `users` AS `User` ON User.MemberID = Mes.fromid
WHERE (Mes.toid = 5 AND Mes.fromid <> 5)
GROUP BY `Mes`.`fromid`
ORDER BY `date` DESC
是:
USERNAME MESSAGE DATE
user2 hello test 2 2012-08-25 05:00:00
user4 hello test 4 2012-08-25 04:00:00
user4 hello test 3 2012-08-25 03:00:00
如果您发现从日期排序是正确的,但是最新消息是不正确的。我想要这样的输出。
USERNAME MESSAGE DATE
user2 hello test 5 2012-08-25 05:00:00
user4 hello test 4 2012-08-25 04:00:00
user4 hello test 3 2012-08-25 03:00:00
我想对最新消息进行排序,而不是消息“ hello test 2”
“你好测试5”
任何人都可以帮助解决我的问题吗?
非常感谢...
最佳答案
应该是这样
SELECT * // -- select the columns you want
FROM Messages a
INNER JOIN
(
SELECT fromid, toid, MAX(`date`) maxDATE
FROM Messages
GROUP BY fromid, toid
) b ON a.fromID = b.fromID AND
a.toid = b.toid AND
a.`date` = b.maxDATE
INNER JOIN users c
ON c.MemberID = a.fromID
WHERE a.toid = 5 AND a.fromid <> 5
ORDER BY `date` DESC