#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
Комментарии:
1. Это выглядит многообещающе, в данный момент немного занято, но скоро попробую.