对话包含消息,一条消息可以发送给2个或更多用户.
目标是按用户检索每个对话的最新消息,以便每个对话中的消息是最新的.
此外message_user.time是用户已读取消息的时间,因此当它等于0时,这意味着尚未读取消息,因此,如果第一时间是message_user且时间等于0且message.time是最大的数字.
我有那些桌子
留言用户
user message time
-----|------------|-------
7 | 1 | 0
8 | 1 | 0
7 | 2 | 300
8 | 2 | 300
7 | 3 | 400
信息
id text conversation time
---|-------------|--------------|----------
1 | blah blah1 | 1 | 200
2 | blah blah2 | 1 | 300
3 | blah blah3 | 2 | 400
4 | blah blah4 | 2 | 500
目标是按对话对消息进行分组,然后获取链接到该对话的最后一个message_user,该用户的时间戳优先较大.
我试过的是这个,但是我没有收到最后一条消息(顺序错误)
SELECT m.user, mu.message, mu.time, mu.id, m.text, m.time as message_time, m.conversation
FROM message_user as mu,message as m
WHERE mu.message=m.id AND mu.user=8
GROUP BY m.conversation
ORDER BY m.time DESC';
然后,下一步将是:
我不知道是否有可能,但是如果命令的顺序可以将message_user输出到时间等于0的位置,然后第二个命令是message.time(我什至不知道在一个请求中是否有可能完善!)
对于用户8,输出应为:
text conversation
-----------|------------
blah blah1 | 1 //because message_user.time = 0 (means message is unread)
blah blah3 | 2 //because message.time is the highest in the conversation
谢谢 !
解决方法:
首先,我建议您重组表以简化此操作,但这是我想您要的…每个对话,仅显示最后创建的转换消息,以及上次阅读该消息的时间和用户(但更喜欢未读)在此).
SELECT m.user as User_Sent, m.conversation, m.text, m.time as Time_Sent
, mu.id, mu.user as User_Read, mu.time as Time_Read
FROM message as m
JOIN (
SELECT mx.conversation, MAX(mx.time) as MaxTime
FROM message as mx GROUP BY mx.conversation
) as mx
On m.conversation = mx.conversation
And m.time = mx.MaxTime
JOIN (
SELECT mu.message, MAX(mu.time) as MaxTime, MIN(mu.time) as MinTime
From message_user as mu
GROUP BY mu.message
) as mux
On m.id = mux.message
JOIN message_user as mu
ON m.id = mu.message
AND CASE WHEN mux.MinTime = 0 THEN mux.MinTime ELSE mux.MaxTime END = mu.time
标签:sql,mysql
来源: https://codeday.me/bug/20191101/1982144.html