Как выбрать отдельные строки с помощью MAX и СГРУППИРОВАТЬ по полю, отличному от uniqe, в MySQL?

#mysql #sql

Вопрос:

Я столкнулся с этим очень простым случаем, когда мне нужно выбрать список разговоров из Conversations таблицы вместе с последним сообщением из Messages таблицы, в котором есть dateCreated поле, не относящееся к uniqe.

После долгих исследований я пришел к этому вопросу:

 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... строками полей.

Я просто не мог найти способ обойти эту проблему, так как основная проблема заключается в том, что когда вы группируетесь по полю и МАКСИМУМ по другому полю, отличному от uniqe, вы не можете всегда выводить только одну строку.

Есть идеи, как получить список уникальных разговоров с последним сообщением (любое из двух сообщений, если у них одна и та же дата)?

1. Пожалуйста, покажите примеры данных и желаемые результаты, а также укажите, какая версия MySQL? (MySQL 8.0 имеет функциональность, которая помогает, но отсутствует в MySQL 5.x)

2. пожалуйста, поделитесь структурой таблицы со сценарием создания и вставки образцов данных и выходных данных, которые вы ожидаете.

Ответ №1:

Используйте row_number() !

 select c.*, m.*    -- or whatever columns you want
from conversations c left join
     (select m.*,
             row_number() over (partition by m.conversationid order by m.dateCreated desc, m.id desc) as seqnum
      from messages m
     ) m
     on m.conversation_id = c.id and
        m.seqnum = 1;

Ответ №2:

Версия MySQL 5.x…

Используйте коррелированный подзапрос, чтобы получить последний идентификатор сообщения (для данного разговора), используя 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

В случае, если два сообщения имеют одинаковую дату, сообщение, которое вы получаете, является недетерминированным / произвольным.

Поэтому вы можете, если захотите, изменить его, чтобы получить самый высокий идентификатор с самой последней даты…

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