我有一个消息表,每个消息都属于一个唯一的对话(conversation_id)。我想从每个对话中选择最新消息。
下面的查询有效,但是由于子查询选择了发送给用户的所有消息,因此效率似乎很低。
SELECT
conversation_id,
to_id,
from_id,
time_sent,
type,
message
FROM (
SELECT
*
FROM
messages
WHERE
to_id = :uid
OR
from_id = :uid
ORDER BY
time_sent
DESC
)
AS
t1
GROUP BY
conversation_id
ORDER BY
time_sent
DESC
如果仅包含一个,GROUP BY
它将选择前(最旧)行:
SELECT
conversation_id,
to_id,
from_id,
time_sent,
type,
message
FROM
messages
WHERE
to_id = :uid
OR
from_id = :uid
GROUP BY
conversation_id
但是,当我这样做GROUP BY
并ORDER BY
在同一查询中时,它将返回每个对话的第一行(最旧),而不是最后一行,因此,该行ORDER BY
不起作用或未达到我的预期。
SELECT
conversation_id,
to_id,
from_id,
time_sent,
type,
message
FROM
messages
WHERE
to_id = :uid
OR
from_id = :uid
GROUP BY
conversation_id
ORDER BY
time_sent
DESC
我很确定我需要子查询,但是如何在不首先选择所有消息的情况下进行呢?
尝试conversation_id
为每个会话将表与带有和max(time_sent)的子查询联接:
SELECT
m.conversation_id,
m.to_id,
m.from_id,
m.time_sent,
m.type,
m.message
FROM messages as m
JOIN
(SELECT conversation_id,
MAX(time_sent) as MAX_time_sent
FROM messages
WHERE to_id = :uid OR from_id = :uid
GROUP BY conversation_id
) AS t1
ON m.conversation_id=t1.conversation_id AND m.time_sent = t1.MAX_time_sent
ORDER BY m.time_sent DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句