mysql - 在 group by 之前使用 limit

Question

我正在使用以下查询获取同一对话的最新 2 条消息:

SELECT *
FROM messages
WHERE conversation_id
IN ( 122806, 122807 )
GROUP BY conversation_id
ORDER BY sent_on DESC
LIMIT 2

返回 message7 和 message3 作为结果。我需要的是获取按 conversation_id 分组的最新 2 条消息，因此结果应该是:

message3
message1
message4
message5

Answer 1

执行此操作的规范方法是在 where 子句中使用计数器:

select m.*
from message m
where 2 >= (select count(*)
            from message m2
            where m2.conversation_id = m.conversation_id and
                  m2.sent_on >= m.sent_on
           );

message(conversation_id, sent_on) 上的索引肯定有助于此查询。这也假设 sent_on 是唯一的。否则，您可以只使用 id。

一个更有效的方法是使用变量:

select m.*
from (select m.*,
             @rn := if(@conversation_id = conversation_id, @rn + 1, 1) as rn,
             @conversation_id := conversation_id
      from message m cross join
           (select @conversation_id := '', @rn := 0) const
      order by conversation_id, sent_on desc
     ) m
where rn <= 2;