php - 按照他们发送或接收的最新消息的顺序选择人员

我在试着做一个聊天系统。它有两个表（聊天室、用户）具有以下模式。聊天桌
用户表

我想根据任何两个用户之间的最新消息选择一个人聊天的所有用户。
例子：
假设我有一个名为X的用户，他/她与a、B、C、D聊天。我必须首先从聊天表中找到a、B、C、D（即所有与X聊天的用户）。然后，对于每个用户A、B、C、D，找到他们从X发送或接收的最新消息的ChatDateTime，并根据它对A、B、C、D进行排序。
聊天表如下所示：

+--------+------------+----------+----------+--------------+
| ChatId | FromUserId | ToUserId | ChatText | ChatDateTime |
+--------+------------+----------+----------+--------------+
| 1      | 2          | 3        | hai      | 12:30        |
| 2      | 3          | 2        | hello    | 12:34        |
| 3      | 3          | 2        | I am X   | 12:38        |
| 4      | 1          | 3        | I am A   | 12:40        |
| 5      | 2          | 3        | I am B   | 12:41        |
| 6      | 4          | 3        | I am C   | 12:42        |
| 7      | 5          | 3        | I am D   | 12:44        |
| 8      | 3          | 4        | Hai 'C'  | 12:50        |
+--------+------------+----------+----------+--------------+

这里的用户id是A=1，B=2，X=3，C=4，D=5。从表中，我们了解到X（UserId=3）已经与A、B、C、D（应该首先选择）进行了对话。最近的对话是在C和X之间（ChatId-8）。所以第一个结果应该是C，然后是D（ChatId-7），然后是B（ChatId-5），最后是A（ChatId-4）。所以选择的顺序应该是C，D，B，A。

最佳答案

请尝试以下操作。。。

SELECT latestChatDateTimeFinder.UserID AS UserID,
       latestChatDateTimeFinder.UserName AS UserName,
       latestChatDateTime AS latestChatDateTime,
       otherUserID AS otherUserID,
       User.UserName AS otherUserName
FROM ( SELECT User.UserID AS UserID,
              User.UserName AS UserName,
              CASE
                  WHEN User.UserID = Chat.FromUserID THEN
                      Chat.ToUserID
                  ELSE
                      Chat.FromUserID
              END AS otherUserID,
              MAX( ChatDateTime ) AS latestChatDateTime
       FROM User
       JOIN Chat ON User.UserID = Chat.FromUserID
                 OR User.UserID = Chat.ToUserID
       GROUP BY User.UserID,
                otherUserID
     ) AS latestChatDateTimeFinder
JOIN User ON latestChatDateTimeFinder.otherUserID = User.UserID
ORDER BY latestChatDateTimeFinder.UserID,
         otherUserID;

此语句以以下子查询开头。。。

SELECT User.UserID AS UserID,
       User.UserName AS UserName,
       CASE
           WHEN User.UserID = Chat.FromUserID THEN
               Chat.ToUserID
           ELSE
               Chat.FromUserID
       END AS otherUserID,
       MAX( ChatDateTime ) AS latestChatDateTime
FROM User
JOIN Chat ON User.UserID = Chat.FromUserID
          OR User.UserID = Chat.ToUserID
GROUP BY User.UserID,
         otherUserID

此子查询根据与INNER JOIN共享值的User或Chat执行FromUserID之间的ToUserID。
然后选择字段。UserID语句将根据CASE建立的共享值选择另一个UserID。
然后根据两个JOIN值对结果数据集进行分组，并通过UserID函数选择与这两个值的组合相对应的最新日期。
这将给我们一个列表，列出每个用户和他们所对应的其他用户，以及最近通信的日期时间。
然后，根据另一个用户现在识别的MAX()值，将此列表连接到主语句中的User，以允许将该用户的名称附加到数据集。
然后从数据集中选择字段，给出别名并排序。
如果您有任何问题或意见，请随时发表相应的评论。
附录
若要将结果限制为一个UserID的结果和所有与之对应的结果，请尝试。。。

SELECT latestChatDateTimeFinder.UserID AS UserID,
       latestChatDateTimeFinder.UserName AS UserName,
       latestChatDateTime AS latestChatDateTime,
       otherUserID AS otherUserID,
       User.UserName AS otherUserName
FROM ( SELECT User.UserID AS UserID,
              User.UserName AS UserName,
              CASE
                  WHEN User.UserID = Chat.FromUserID THEN
                      Chat.ToUserID
                  ELSE
                      Chat.FromUserID
              END AS otherUserID,
              MAX( ChatDateTime ) AS latestChatDateTime
       FROM User
       JOIN Chat ON ( User.UserID = Chat.FromUserID OR
                      User.UserID = Chat.ToUserID )
                AND User.UserID = targetUser
       GROUP BY otherUserID
     ) AS latestChatDateTimeFinder
JOIN User ON latestChatDateTimeFinder.otherUserID = User.UserID
ORDER BY otherUserID;

... 或者。。。

SELECT latestChatDateTimeFinder.UserID AS UserID,
       latestChatDateTimeFinder.UserName AS UserName,
       latestChatDateTime AS latestChatDateTime,
       otherUserID AS otherUserID,
       User.UserName AS otherUserName
FROM ( SELECT User.UserID AS UserID,
              User.UserName AS UserName,
              CASE
                  WHEN User.UserID = Chat.FromUserID THEN
                      Chat.ToUserID
                  ELSE
                      Chat.FromUserID
              END AS otherUserID,
              MAX( ChatDateTime ) AS latestChatDateTime
       FROM User
       JOIN Chat ON User.UserID = targetUser
                AND User.UserID = targetUser
       GROUP BY otherUserID
     ) AS latestChatDateTimeFinder
JOIN User ON latestChatDateTimeFinder.otherUserID = User.UserID
ORDER BY otherUserID;

请注意，User（用于引用相关targetUser的值）可以是显式值或变量。