我的查询:
SELECT *
FROM messages_status mst
INNER JOIN messages msg
ON mst.msg_id=msg.id
WHERE mst.user_id = 1
AND mst.status NOT IN (0,3)
ORDER BY
msg.created_at DESC
将显示在这个sqlfiddle中:http://sqlfiddle.com/#!2/be010/1/0
你有两个表:messages 和 message_status
首先包含消息数据,如内容、sender_id、创建时间和对话 id。
第二个具有特定的用户消息状态,是已读取还是已删除。
我想获取每次对话的最后一条消息
有任何想法吗?
看一下这个:
SELECT *
FROM messages_status mst
INNER JOIN (SELECT messages.* FROM messages INNER JOIN (
SELECT sender_ID,conv_id,MAX(created_at) as maxtime FROM messages
GROUP BY sender_ID,conv_id) as t1 ON
t1.sender_id = messages.sender_id AND t1.conv_id = messages.conv_id AND
t1.maxtime = messages.created_at) msg
ON mst.msg_id=msg.id
WHERE mst.user_id = 1 AND mst.status NOT IN (0,3)
ORDER BY msg.created_at DESC
这是link
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)