连接表的Mysql查询排序

Question

我有一个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“，即"hello test 5”。

有人能帮我解决问题吗？

非常感谢你..。

Answer 1

应该是这样的，

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