在MySQL中选择每个对话的最后一条消息

Question

我的问题：

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，第一个包含消息数据，如内容、发件人ID、创建时间和会话ID。第二个具有特定用户消息状态，即它是否已读或删除。

我想要获得每个对话的最后一条消息，有什么想法吗？

- Tzook Bar Noy

Answer 1

看看这个：

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

这是链接

- VahidSH

---

谢谢！但是你有什么想法，为什么我每个对话框会得到两行而不是一行？ - Tzook Bar Noy

Answer 2

这里是Fiddle。http://sqlfiddle.com/#!2/be010/116

这是你可以大多数情况下使用的查询：

SELECT *
FROM 
(SELECT messages.id as id, 
messages.conv_id as conv_id,
max(messages.created_at) as created_at FROM messages,messages_status
WHERE messages.id = messages_status.msg_id
AND messages_status.user_id = 1 
AND messages_status.status NOT IN (0,3)
GROUP BY messages.conv_id) max_created
INNER JOIN messages 
ON messages.created_at = max_created.created_at
INNER JOIN  messages_status mst 
ON mst.msg_id=max_created.id
GROUP BY messages.conv_id
ORDER BY max_created.created_at DESC;

- Neels

---

谢谢，但是正如你在结果中看到的，你只得到了一条对话消息，而实际上有两个对话。 - Tzook Bar Noy

好的，我弄清楚了我做错了什么。已经编辑了上面的查询和Fiddle。 - Neels

Answer 3

你想要获取每个对话的最后一条消息，可以尝试以下方法：

select *
from messages msg
inner join
(
  select max(created_at) created_at
  from messages
  group by conv_id
) m2
  on msg.created_at = m2.created_at

Inner JOIN messages_status mst ON msg.id=mst.msg_id 
WHERE mst.user_id = 1 AND mst.status NOT IN (0,3)

http://sqlfiddle.com/#!2/be010/89

- Abhik Chakraborty

Answer 4

SQLFIDDLEExample

查询：

SELECT msg.*
FROM messages_status mst
INNER JOIN messages msg ON mst.msg_id=msg.id
LEFT JOIN messages ms2
 ON ms2.conv_id = msg.conv_id
 AND ms2.created_at > msg.created_at
WHERE mst.user_id = 1 
 AND mst.status NOT IN (0,3)
 AND ms2.created_at is null

结果：

| ID | SENDER_ID | CONV_ID |       CONTENT |                   CREATED_AT |                   UPDATED_AT |
|----|-----------|---------|---------------|------------------------------|------------------------------|
| 16 |         3 |       3 |         hy :) | March, 26 2014 16:38:17+0000 | March, 26 2014 16:38:17+0000 |
| 14 |         1 |       1 | kjhkjhkjh  kl | March, 26 2014 16:16:25+0000 | March, 26 2014 16:16:25+0000 |

- Justin

---

唯一不涉及第二个查询的解决方案。运作得非常好。 - Mat