#mysql #database #join #group-by #chat
Вопрос:
Всем добрый день, в настоящее время я борюсь с написанием запроса mysql, который должен создать группу чата из сообщений. Приложение, которое я создаю, берет данные из другой службы, поэтому мне не нужно также управлять группами чатов, мне нужно получать эту информацию из самих сообщений.
Моя таблица сообщений выглядит так:
| ID | sendAddr | receiveAddr | Последнее сообщение | текст | Читать |
|---|---|---|---|---|---|
| 1 | Ccbbd6uUZF2GD5wE5LEfjGPA3YWPjoLC6P | CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | 2021-06-02 12:57:39 | тест3 | 0 |
| 5 | Ccbbd6uUZF2GD5wE5LEfjGPA3YWPjoLC6P | CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | 2021-06-02 13:00:44 | тест3 | 0 |
| 7 | CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | Ccbbd6uUZF2GD5wE5LEfjGPA3YWPjoLC6P | 2021-06-10 23:13:59 | testVPS | 0 |
| 8 | CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | CYfMNQ62u1qwhCBqXzcmeJ8D9j4Yc1Pwef | 2021-06-10 20:03:59 | неко | 0 |
| 9 | CYfMNQ62u1qwhCBqXzcmeJ8D9j4Yc1Pwef | CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | 2021-06-10 21:03:59 | тест | 0 |
Мои единственные входные данные , которые я отправил из своего мобильного приложения receiveAddr , так что мне нужно добавить это в группу всего, что содержит адрес в любом sentAddr или receiveAddr , вот представление того, чего я хочу достичь
| пользователь | Другой участник | непрочитанный | Последнее сообщение | текст |
|---|---|---|---|---|
| CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | Ccbbd6uUZF2GD5wE5LEfjGPA3YWPjoLC6P | 5 | 2021-06-10 23:13:59 | testVPS |
| CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF | CYfMNQ62u1qwhCBqXzcmeJ8D9j4Yc1Pwef | 1 | 2021-06-10 21:03:59 | тест |
В нем должна быть последняя text подсчитанная сумма 0 в read столбце и lastMessage время последнего сообщения
Я придумал что-то вроде того, что работает, если пользователь мобильного приложения будет просто получать сообщения, которые будут такими:
ВЫБЕРИТЕ sentAddr, ПОДСЧИТАЙТЕ(ЕСЛИ(campus.messages.read = 0, 1, NULL)) как непрочитанное, макс(Время приема) как последнее сообщение, макс(текст) как текст ИЗ campus.messages, ГДЕ (ВЫБЕРИТЕ макс(время приема) ИЗ campus.messages) И receiveAddr = ‘CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF’ ГРУППА ПО sentAddr, receiveAddr
Но, к сожалению, это не дает мне текст из последнего сообщения и даже не принимает во внимание, что если один и тот же пользователь отправляет какое-то сообщение, оно отображается как другая группа, что на самом деле не очень полезно. Я также придумал кое-что, что объединяет sendAddr и receiveAddr в одну группу, независимо от того, находится ли она на стороне отправки или получения:
СГРУППИРУЙТЕ ПО КОНКАТУ(НАИМЕНЬШИЙ(receiveAddr,sentAddr),’ ‘, НАИБОЛЬШИЙ(receiveAddr, sentAddr))
Однако в этом случае я не могу добавить это в верхний запрос, он не извлекает последний текст, что бесполезно. Итак, есть ли шанс сделать это с помощью одного запроса, каким бы длинным и запутанным он ни был? И просто напомним, что только входные данные-это адрес пользователя, который может быть адресом отправки или получения из таблицы. Так есть ли какое-нибудь решение этой проблемы?
Версия базы данных mysql является 8.0.25
Комментарии:
1. что это
select version();показывает?2. @ysth это версия
8.0.25
Ответ №1:
Я понял это, вот оно:
SELECT myMessages.user, myMessages.otherParticipant, groupList.unread,
groupList.lastMessage, myMessages.text FROM (SELECT IF(sentAddr = ?,
sentAddr, receiveAddr) as user, IF(receiveAddr = ?, sentAddr,
receiveAddr) as otherParticipant, receiveTime, campus.messages.text,
campus.messages.read FROM campus.messages) myMessages INNER JOIN (SELECT
otherParticipant, COUNT(IF(myMessages2.read = 0, 1, NULL)) as unread,
max(receiveTime) as lastMessage FROM (SELECT IF(receiveAddr = ?,
sentAddr, receiveAddr) as otherParticipant, receiveTime,
campus.messages.read FROM campus.messages WHERE sentAddr = ? or receiveAddr = ?) as myMessages2 GROUP BY
otherParticipant) groupList ON myMessages.otherParticipant =
groupList.otherParticipant AND myMessages.receiveTime =
groupList.lastMessage