私人聊天系统MYSQL查询显示发件人/接收者的最后一条消息 [英] Private chat system MYSQL query to display last message of sender/receiver
问题描述
这里我扩展我以前的问题。
Here I am extending my previous question.
此外,我又创建了一个名为users的表将获得用户信息。
In addition, I created one more table called users from where I will get users info.
消息表
message_id|sender_id||receiver_id|message_text| created_time
1 101 102 Message A 2012-06-07 08:07:18
2 101 102 Message B 2012-06-07 08:10:20
3 103 102 Message C 2012-06-07 08:12:43
users表格:
id | name
101 bob
102 jack
103 mark
我可以得到以下结果:
name|message_text | created_time
mark message C 2012-06-07 08:12:43
bob message B 2012-06-07 08:10:20
使用此查询:
SELECT * FROM messages,users
WHERE messages.receiver_id = 102
AND messages.sender_id=users.id
AND messages.created_time IN
(SELECT MAX(created_time)
FROM messages
GROUP BY sender_id)
ORDER BY messages.created_time DESC
现在我想要的结果是
如果jack(id:102)回复mark(id:103)那么我该如何得到这个输出:
if jack(id:102) reply to mark(id:103) then how can I get this output:
name|message_text | created_time
mark message D 2012-06-07 08:12:48
bob message B 2012-06-07 08:10:20
注意:这里的信息D和时间戳是他替换到标记的。
NOTE:HERE "MESSAGE D" AND TIME STAMP IS OF JACK WHICH HE REPLIED TO MARK.
其中message_text字段将显示标记和jack之间的最后一条消息
created_time字段将显示消息创建时间
,名称字段将显示名称杰克正在发送或接收消息的人。
where message_text field will display the last message between mark and jack created_time field will show message created time and name field will show the name of person to whom jack is sending or receiving messages.
我想我们需要修改/拆分表,但不知道如何以及什么查询会执行此任务。
I am thinking we need to modify/split tables but don't know how and what query will do this task.
推荐答案
如果这是群组聊天,不限于像 messages.receiver_id = 102
If this is a group chat don't limit to a receiver_id like messages.receiver_id = 102
SELECT users.name,
temp.MESSAGE_TEXT,
temp.created_time
FROM
(SELECT sender_id, MESSAGE_TEXT, created_time
FROM messages
WHERE created_time IN
(SELECT MAX(created_time)
FROM messages
GROUP BY sender_id)) AS temp
LEFT JOIN users ON temp.sender_id = users.id;
显示每个发送者发送的最后一条消息
show last message each sender sent
这篇关于私人聊天系统MYSQL查询显示发件人/接收者的最后一条消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!