时间戳

首页 > 解决方案 > 如何在 MySQL 的非唯一字段上使用 MAX 和 GROUP BY 选择单行？

mysql - 如何在 MySQL 的非唯一字段上使用 MAX 和 GROUP BY 选择单行？

问题描述

我遇到了一个非常简单的案例，我需要从Conversations表格中选择一个对话列表以及表格中的最新消息Messages- 它具有非唯一dateCreated字段。

经过长时间的研究，我想出了这个查询：

SELECT
Conversations.id,
dateCreated,
`name`,
lastMessageId,
lastMessageDate,
lastMessagePayload
FROM Conversations
LEFT JOIN (
  SELECT
    id AS lastMessageId,
    m1.conversationId,
    payload AS lastMessagePayload,
    m1.dateCreated AS lastMessageDate,
  FROM Messages AS m1
  INNER JOIN (
    SELECT conversationId, MAX(dateCreated) AS mdate FROM Messages GROUP BY conversationId
  ) AS m2
  ON m1.conversationId = m2.conversationId AND m1.dateCreated = m2.mdate
) AS msg2
ON msg2.conversationId = Conversations.id
ORDER BY dateCreated DESC

查询运行良好，但如果同一对话中的两条最新消息具有完全相同的dateCreated字段，则此查询将输出两个具有相同id但不同lastMessage...行字段的对话。

我只是找不到解决此问题的方法，因为主要问题是当您对一个字段执行 GROUP BY 并在另一个非唯一字段上执行 MAX 时，您不能总是只退出一行。

知道如何获取具有最新消息的唯一对话列表（如果它们具有相同的日期，则两者的任何消息）？

标签： mysqlsql

解决方案

MySQL 5.x 版本...

使用相关的子查询来获取最新的消息 ID （对于给定的对话），使用ORDER BY和LIMIT 1

SELECT
  Conversations.Conversations.id,
  Conversations.dateCreated,
  Conversations.`name`,
  Messages.id                AS lastMessageId,
  Messages.payload           AS lastMessagePayload,
  Messages.dateCreated       AS lastMessageDate,
FROM
  Conversations
LEFT JOIN
  Messages
    ON Messages.id = (
        SELECT lookup.id
          FROM Messages AS lookup
         WHERE lookup.conversationId = Conversations.id
      ORDER BY lookup.dateCreated DESC
         LIMIT 1
    )
ORDER BY
  Conversations.dateCreated DESC

如果两条消息具有相同的日期，您收到的消息是不确定的/任意的。

因此，如果您愿意，可以将其更改为从最近日期获取最高 id ...

      ORDER BY lookup.dateCreated DESC, lookup.id DESC
         LIMIT 1

推荐阅读