按其发送或接收的最新消息的顺序选择人员
我想做一个聊天系统。它有两个表(聊天，用户)，具有以下模式。聊天台
用户表
我希望根据任何两个用户之间的最新消息，选择与一个人聊天的所有用户。
示例：
假设我有一个名为X的用户，他/她已经与A、B、C、D进行了交谈。我必须首先从聊天表中找到A、B、C、D(即所有与person 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 |
+--------+------------+----------+----------+--------------+这里的用户be是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。
回答 4
Stack Overflow用户
发布于 2017-05-02 05:57:59
请试试看..。
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该子查询在User和Chat之间执行一个基于FromUserID或ToUserID与UserID共享一个值的FromUserID。
然后，它选择字段。CASE语句将根据JOIN创建的共享值选择其他UserID。
然后根据两个UserID值对结果数据集进行分组，并通过MAX()函数选择与这两个值组合相对应的最新日期。
这将给我们一个列表，列出每个用户和他们对应的其他用户，以及最近通信的日期。
然后，根据其他用户现在标识的UserID值，在main语句中将该列表加入到UserID中，以便将该用户的名称附加到数据集中。
然后从数据集中选择字段，给出别名并排序。
如果您有任何问题或意见，请随时发表相应的评论。
附录
若要将结果限制为一个User的结果和所有与之对应的结果，请尝试.
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;请注意，targetUser (用于引用所讨论的User的值)可以是显式值，也可以是变量。
Stack Overflow用户
发布于 2017-05-02 05:38:28
第一部分-所有与person X聊天的用户
第二部分与该用户相关的所有用户
我把这两部分结合在一起。
select u.UserName,c.toUserId from userTable u inner join chatTable c where UserID IN (select toUserId from chatTable where fromUserId = 'X' order by ChatDateTime desc )
UNION ALL
select u.UserName,c.toUserId from userTable u inner join chatTable c where UserID IN (select fromUserId from chatTable where fromUserId = 'X' order by ChatDateTime desc ) Stack Overflow用户
发布于 2017-05-02 05:40:09
尝试像这样的时候
select max(chatdatetime) as chatdatetime,sec_user from
(select chatid,chatdatetime,
case when fromuserid='X' then touserid
when touserid='X' then fromuserid
end as sec_user
from chat
where fromuserid='X' or touserid='X'
) t
group by sec_userhttps://stackoverflow.com/questions/43730662
复制相似问题
腾讯云开发者
