Предложение GROUP BY и содержит проблему с неагрегированным столбцом, решаемую без отключения полной переменной groupby из SQL

ProgramBox

Предложение GROUP BY и содержит проблему с неагрегированным столбцом, решаемую без отключения полной переменной groupby из SQL

Post author:admin
Запись опубликована:7 апреля, 2023
Post category:Вопросы по программированию

#mysql #sql

Вопрос:

Я столкнулся с некоторой проблемой агрегации groupby в моем запросе объединения ниже:

 SELECT chat_tbl.chatrec_id,
       chat_tbl.senderid,
       chat_tbl.receiverid,
       chat_tbl.msg,
       chat_tbl.chat_time,
       chat_tbl.chatuser_strid, 
       chatuser_link_tbl.str_id, 
       chatuser_link_tbl.modifytime 
FROM chat_tbl 
INNER JOIN chatuser_link_tbl ON chat_tbl.chatuser_strid = chatuser_link_tbl.str_id 
                            AND (chat_tbl.senderid=393 OR chat_tbl.receiverid=393) 
GROUP by chat_tbl.chatuser_strid 
ORDER by chatuser_link_tbl.modifytime DESC

Я знаю, что могу отключить only_full_group_by настройку, выполнив следующее:

 set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Но это неправильный метод, он отменяет настройки, и это снова удаляется после каждого перезапуска сервера, поэтому я хочу исправить свой запрос. Пожалуйста, кто-нибудь может помочь мне правильно написать этот запрос объединения из 3 таблиц.

Заранее спасибо.

Обновление 1: я использую этот запрос, чтобы перечислить все чаты, выполняемые текущим пользователем с другими пользователями.

У меня есть 2 таблицы для этого, в chatuser_link_tbl есть ссылка на чат, созданная между 2 пользователями, например, 11-18, 12-22, т. Е. объединены 2 идентификатора пользователя с dash — и таблица чата содержит фактические данные чата.

Я использую group by для группировки всех чатов с использованием chatuser_strid, чтобы получить только одну последнюю запись. Я показываю только последнее сообщение чата с указанием даты и времени.

Короче говоря, это то же самое, что список чатов WhatsApp (первый домашний экран) или список чатов Facebook Messenger. Которая отображает данные пользователя с их последним сообщением в чате.

1. В вашем SELECT списке могут быть только значения, которые являются либо агрегатной функцией (например, MAX() , SUM() , COUNT() и т.д.), Либо поля, которые присутствуют в ваших GROUP BY , при использовании сгруппированных запросов.

2. Почему вы вообще используете GROUP BY предложение здесь?

3. @Qirel Не могли бы вы переписать этот запрос с вашими предложениями, чтобы сделать его правильным?

4. Я предлагаю вам взять руководство по SQL и понять, что все делает. Вы понимаете, что GROUP BY делает? Потому что я не понимаю, почему вы использовали ее здесь. И я готов поспорить, что если вы удалите ее, вы добьетесь некоторого прогресса..

5. Сначала вы должны объяснить значение запроса. Чего именно вы пытаетесь достичь, и какие поля обеих таблиц вам нужно извлечь для вашего приложения

Ответ №1:

Поскольку ваш запрос предназначен для работы с одним пользователем (отправителем или получателем, равным 393), вы можете просто удалить group by и добавить ограничение

 SELECT chat_tbl.chatrec_id,
       chat_tbl.senderid,
       chat_tbl.receiverid,
       chat_tbl.msg,
       chat_tbl.chat_time,
       chat_tbl.chatuser_strid, 
       chatuser_link_tbl.str_id, 
       chatuser_link_tbl.modifytime 
FROM chat_tbl 
INNER JOIN chatuser_link_tbl ON chat_tbl.chatuser_strid = chatuser_link_tbl.str_id 
                            AND (chat_tbl.senderid=393 OR chat_tbl.receiverid=393) 
ORDER by chatuser_link_tbl.modifytime DESC
LIMIT 1

1. Если вы делали это только для тестирования, и вы хотите, чтобы ваш окончательный запрос выполнялся для всех пользователей. Вы можете использовать оконные функции (LAST_VALUE) или боковое соединение, если ваша версия MySQL поддерживает любую из них. Если это так, я могу расширить свой ответ.

2. Этот запрос выдаст только отдельные записи, я хочу, чтобы все записи таблицы chat имели либо идентификатор получателя = 393, либо идентификатор отправителя = 393. Например, вы могли бы общаться с несколькими пользователями, а не только с одним пользователем. Например, проверьте Facebook messanger, где отображается список всех пользователей и их последнее сообщение, с которым вы общались. надеюсь, теперь это ясно.

3. В этом случае ответ Гордона не должен быть именно тем, что вам нужно. Единственная проблема может возникнуть из-за chatuser_strid, который будет отображаться дважды для пар (например, 11-22 и 22-11)

Ответ №2:

Я думаю, вы хотите использовать для этого оконные функции:

 SELECT *
FROM (SELECT c.*, cl.str_id, cl.modifytime,
             ROW_NUMBER() OVER (PARTITION BY c.chat_user_strid ORDER BY cul.modifytime DESC)
      FROM chat_tbl c JOIN
           chatuser_link_tbl cul
           ON c.chatuser_strid = cul.str_id AND
              393 IN (c.senderid, c.receiverid) 
     ) c
WHERE seqnum = 1;

Ответ №3:

Я исправил проблему самостоятельно. Приведенный ниже запрос сработал для меня, чтобы получить желаемый результат.

Мне помоглоANY_VALUE.

 SELECT chat_tbl.chatuser_strid, ANY_VALUE(chat_tbl.chatrec_id),ANY_VALUE(chat_tbl.senderid),ANY_VALUE(chat_tbl.receiverid),ANY_VALUE(chat_tbl.msg),ANY_VALUE(chat_tbl.chat_time), ANY_VALUE(chatuser_link_tbl.str_id)       
FROM chat_tbl      
INNER JOIN chatuser_link_tbl      
ON chat_tbl.chatuser_strid = chatuser_link_tbl.str_id AND (chat_tbl.senderid=".$userid." OR chat_tbl.receiverid=".$userid.")       
GROUP by chat_tbl.chatuser_strid      
ORDER by ANY_VALUE(chatuser_link_tbl.modifytime) DESC