为mysql中的每个会话选择会话的最后一条消息

问题描述：

我的查询:

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

will show up in this sqlfiddle: http://sqlfiddle.com/#!2/be010/1/0

您有2个表:messages和message_status 首先包含消息数据，例如内容，sender_id，创建时间和对话ID. 第二个具有特定的用户味精状态，是已读取还是已删除.

You have there the 2 tables: messages and message_status first contains message data like content, sender_id, created time, and conversation id. Second has specific user msg status, is it read or deleted.

我想获取每个对话的最新消息

有什么想法吗?

答

检查一下:

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

这是链接

为mysql中的每个会话选择会话的最后一条消息

相关推荐