mysql - 基于加入结果的订单选择(发送最后一条消息时的排序对话)

我在Hibernate中为消息传递组件创建一个SQL查询。我的想法是我要查询用户的对话，并按上次发送消息的日期排序。

我有两个表：
conversations

messages


在我的选择查询中，我正在尝试类似的操作，但是在发送的最后一条消息中，排序从未发生。

String sql =
            "SELECT * FROM conversations " +
            "JOIN messages ON messages.conversation_id = conversations.id "+
            "WHERE (conversations.creator_id = :userId OR conversations.to_id = :userId)" +
            "GROUP BY messages.conversation_id "+
            "ORDER BY messages.created DESC";

最佳答案

此问题是由于GROUP BY子句的行为在MySQL特定的扩展引起的。其他数据库将引发错误……类似于SELECT列表中的聚合。”（如果在sql_mode中包含ONLY_FULL_GROUP_BY，我们可以使MySQL引发类似的错误。）

表达式messages.created的问题是引用GROUP BY中不确定行中的值。 ORDER BY操作在GROUP BY操作之后的处理过程中发生得多。

要获取为每个组创建的“最新”消息，请使用聚合表达式MAX(messages.created)。

要从同一行中获取其他值，要复杂一些。

假设created在给定的conversation_id组中是唯一的（或者，如果不能保证它不是唯一的，则可以为created返回具有相同值的多行...

获取每个created的最新conversation_id

SELECT lm.conversation_id
     , MAX(lm.created) AS created
  FROM conversation lc
  JOIN message lm
    ON lm.conversation_id = lc.id
 WHERE (lc.creator_id = :userId OR lc.to_id = :userId)
 GROUP BY lm.conversation_id

您可以将其用作内联视图，以使用最新的created获取整行

SELECT c.*
     , m.*
  FROM ( SELECT lm.conversation_id
              , MAX(lm.created) AS created
           FROM conversation lc
           JOIN message lm
             ON lm.conversation_id = lc.id
          WHERE (lc.creator_id = :userId OR lc.to_id = :userId)
          GROUP BY lm.conversation_id
       ) l
  JOIN conversation c
    ON c.id = l.conversation_id
  JOIN messages m
    ON m.conversation_id = l.conversation_id
   AND m.created         = l.created
 WHERE (c.creator_id = :userId OR c.to_id = :userId)

笔记：

您可以添加ORDER BY子句以对返回的行进行排序（如果需要）。

外部查询上的WHERE子句可能是多余的，并且不必要。

我们更喜欢避免使用SELECT *，而是更明确地列出要返回的表达式。

关于mysql - 基于加入结果的订单选择(发送最后一条消息时的排序对话)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37930515/