我有一个表messages用于用户之间的对话。
表列的名称为:

messageID | fromUser | forUser | message | submitDate | seen

样本数据:
1  |  1 | 2 | "hi"             | "12341" | 0
2  |  2 | 1 | "hi"             | "12342" | 0
3  |  1 | 3 | "hi"             | "12343" | 0
4  |  1 | 4 | "hi 4"           | "12344" | 0
5  |  2 | 1 | "hello"          | "12345" | 0
6  |  1 | 2 | "hello how r u?" | "12346" | 0
7  |  3 | 1 | "hello user 1"   | "12345" | 0

如何编写查询以查找我和系统中其他每个用户之间发送的最后一条消息?
我是说最后的信息是:
between user 1 and 2 : "hello how r u?"
between user 1 and 3 : "hello user 1"
between user 4 and 1 : "hi 4""

我的问题:
$query = "SELECT DISTINCT `fromUser`, `forUser`, `message`, `seen`,
                          `username`, `userPhoto`
          FROM `messages`,`user`
          WHERE (`forUser`= '$myUserID' OR `fromUser`= '$myUserID')
                AND (((`forUser`= `userID`) AND (`forUser` !=  '$myUserID'))
                   OR ((`fromUser`= `userID`)
                   AND (`fromUser` !=  '$myUserID')))
          ORDER BY `submitDate` DESC";

但是这个查询需要获取对话中的所有消息!我只需要最后一条信息。

最佳答案

您可以使用以下查询为每个用户与其他某个用户进行userID = 1对话的用户获取最新消息:

SELECT messageID, message, submitDate, otherUser
FROM (
   SELECT messageID, message, submitDate,
          @row_number:=CASE WHEN @other=otherUser THEN @row_number+1
                            ELSE 1
                       END AS row_number,
          @other:=otherUser AS otherUser
   FROM (
      SELECT messageID, message, submitDate,
             IF(1 = fromUser, forUser, fromUser) as otherUser
      FROM messages AS m
      WHERE 1 IN (fromUser,forUser)
      ORDER BY otherUser, submitDate DESC) t ) s
WHERE s.row_number = 1

SQL Fiddle Demo
内部查询:
SELECT messageID, message, submitDate,
       IF(1 = fromUser, forUser, fromUser) as otherUser
FROM messages AS m
WHERE 1 IN (fromUser,forUser)
ORDER BY otherUser, submitDate DESC

用于返回用户使用userID = 1发送或接收的消息列表。计算列otherUser只包含与userID = 1用户对话所涉及的其他用户(发送方或接收方)。
在外部查询中,变量@row_number用于模拟sql server中可用的ROW_NUMBER() OVER (PARTITION BY otherUser ORDER BY submitDate DESC)
最后,外部查询使用@row_number计算出的数字来选择每个otherUser的最新消息。

10-07 19:10
查看更多