Форум пользователей MySQL
Задавайте вопросы, мы ответим
Страниц: 1
#1 14.11.2012 16:12:09
распространенная ошибка в запросах с group by
В запросе с группировкой в части перечисления полей (то, что идет после SELECT) по стандарту SQL можно указывать ТОЛЬКО те поля, по которым идет группировка, или которые используются с групповыми функциями (sum, max, group_concat, . )
MySQL не генерирует ошибку, если запрос не удовлетворяет данному правилу. Однако результат часто бывает не тот, который ожидается, так как для полей без групповых функций и не указанных в части GROUP BY выбирается произвольная строка из группы.
Для примера рассмотрим таблицу сообщений, имеющую поля (`user_id`, `post`, `time`), в которой хранится id пользователя, текст сообщения и время добавления сообщения. Пусть мы хотим выбрать последнее сообщение каждого юзера. Порой можно увидеть такие конструкции:
Оба этих запроса неверные. В первом для каждого user_id будет выбрана произвольная строка с данным user_id после чего эти строки будут отсортированы по времени. Во втором, для каждого user_id будет выбрано max(`time`) и значение поля `post` из случайной строки с данным user_id, а не из той, которая соответствует max(`time`). А если результат правильный, то это не более чем случайность.
Проиллюстрируем ситуацию на простом примере.
MariaDB [ test ] > select user_id, post, ` time ` from test_table;
+ ———+———+———————+
| user_id | post | time |
+ ———+———+———————+
| 1 | post 1 | 2012 -10 -14 16 : 51 : 26 |
| 1 | post 2 | 2012 -11 -12 16 : 51 : 26 |
| 1 | post 3 | 2012 -11 -15 16 : 51 : 26 |
+ ———+———+———————+
3 rows in set ( 0.14 sec )
MariaDB [ test ] > select user_id, post, max ( ` time ` ) from test_table group by user_id;
+ ———+———+———————+
| user_id | post | max ( ` time ` ) |
+ ———+———+———————+
| 1 | post 1 | 2012 -11 -15 16 : 51 : 26 |
+ ———+———+———————+
Как видим значение поля `post` выбрано «неверно». В кавычках потому что неверно с человеческой точки зрения — мы ожидали увидеть значение, соответствующее максимальному времени, т.е. ‘post 3’. А сервер MySQL взял значение поля post из первой попавшейся строки с user_id =1, так как никаких указаний относительно этого поля ему не поступало.
Правильным решением данной задачи будет найти сначала комбинации (`user_id`,`time`), а по ним уже выбрать недостающее поле `post`. Для простоты предположим, что у пользователя не может быть двух одновременных сообщений, т.е. комбинация (`user_id`,`time`) однозначно идентифицирует строку. Тогда нужный нам запрос будет выглядеть так:
При определенных условиях существует способ решить задачу без использования JOIN. Пусть поле `time` имеет тип данных DATETIME, т.е. принимает значения вида ‘2012-02-15 01:47:19’. Тогда мы знаем, что значение этого поля всегда будет иметь ширину в 19 символов. В этом случае мы можем искать максимум не от поля `time`, а от строки, склеивающей значения полей `time` и `post`, а потом результат разбить назад на составляющие.
Источник
Как работает GROUP BY в MySQL?
Вопрос по sql по клаузуле GROUP BY .
Рассмотрим группировку по ОДНОМУ столбцу. Пример:
То есть, в столбце DEPARTMENT_ID ищется уникальное (похоже на DISTINCT ) значение отдела, например, 30, затем ищутся все строки, где упоминается отдел 30 в данной таблице, из этих строк берутся значения из столбца SALARY и суммируются ( SUM ). Потом ищется другой покупатель и все повторяется. В итоге я получаю сколько получил вообще денег каждый отдел.
Не понимаю момент: у меня есть 6 строк, в которых есть столбец DEPARTMENT_ID со значением 30. Какая из строк пойдет в таблицу- SELECT и почему? То есть, в таблице Employees было шесть строк с DEPARTMENT_ID 30, а в таблице- SELECT такая строка только одна. Как вообще эта группировка работает?
Рассмотрим группировку по двум столбцам. Ее я вообще не понимаю. Даже картинки нормальной не нашел, из которой было бы понятно. Просмотрел кучу статей и книг по этому вопросу, но не понял ничего.
2 ответа 2
Добавлю с примером запросов и вывода GROUP BY по двух полях. Смотреть можно по таблице в которую, например, сохраняеться какой пользователь (user_id) вносил деньги, на какой счет (account) и сколько (balance). Например, нужно узнать сколько каждый пользователь внес на каждый из своих счетов.
Работает GROUP BY по двум полям так же как и по одному, сначала сортирует, а потом смотрит, если оба значения в строке такие же как и в предыдущей строке, тогда групирует эти строки. Если хотя бы одно значение не такое как в предыдущей строке, тогда групировки не будет. Для 3 и больше полей GROUP BY работает так же.
В выборку после group by не попадет ни одна из изначальных строк. На выходе агрегат — сумма данных в нужном разрезе. К колонкам, к которым вы явно не применили никаких групповых функций (таких как sum() ), будет применена функция «первое попавшееся». Причем только в MySQL и только при выключенной опции ONLY_FULL_GROUP_BY . В остальных СУБД запрос, в котором хотя бы к одной колонке, не являющейся разрезом указанным в group by, «забыли» применить групповую функцию выдаст ошибку.
Как работает group by можно прикинуть в экселе. Запишите данные на лист, отсортируйте по тем полям, которые должны быть в group by . Читая отсортированные данные подряд в любом случае когда значение в очередной строке в колонках, указанных в group by отличается от значений в предыдущей — вставьте новую строку, скопируйте значения колонок group by , а в остальные поместите формулы вроде СУММ() ячеек группы под которой подводится итог. Результат group by — это именно эти вставленные итоговые записи. СУБД работает примерно по такому же алгоритму — сначала сортирует, потом суммирует идущие подряд одинаковые записи.
Добавлю про MySQL — он все таки слишком вольно к этому относится. Старайтесь всегда явно применять групповые функции ко всем колонкам, что бы самому понимать что именно в них окажется, ибо ‘первое попавшееся’ ни чем не стандартизировано и может меняться от версии к версии и в зависимости от физического расположения записей на диске и плана выполнения запроса.
Источник
Sql GROUP BY и НЕ РАБОТАЕТ
У меня есть этот SQL-запрос, который дает мне результат, который не является полным (половина результата). Я хочу получить информацию о всех учениках из того же класса, который находится из таблицы учеников, а затем группировать их по классу.
Это код запросов:
В последнем запросе также отображается только одна информация, которая является первым именем студента класса «jss1» в таблице, но на этот раз показывает, что в jss1 есть 17 учеников, которые находятся в jss1
Как я могу манипулировать запросом, чтобы отображать все имена студентов в одном классе?
Это структура таблицы:
GROUP BY применяется, когда вы хотите сгруппировать некоторую информацию об агрегатных функциях (в качестве последнего запроса, в котором вы хотите узнать COUNT).
Итак, когда у вас есть сводная функция, и если вы хотите показать с ней скалярные поля, они должны быть помещены в предложение GROUP BY .
Предложение HAVING используется для применения условия агрегированного значения (например, я хочу знать все строки с COUNT > 1), напишу: HAVING COUNT() > 1 , поэтому все ваши запросы должны использовать WHERE
НЕПРАВИЛЬНО
СТАНОВИТСЯ
НЕПРАВИЛЬНО
СТАНОВИТСЯ
НЕПРАВИЛЬНО
СТАНОВИТСЯ
Если вы хотите перечислить учащихся в данном классе, выполните следующие действия:
Запросить список учеников в каждом классе
Вы видите, что эти два запроса решают разные цели. Предложение group by используется для группировки связанных данных, и вы должны иметь все столбцы, упомянутые в group by , которые вы выбираете.
Вы также можете получить список учеников определенного класса, используя группу, и ваш запрос будет выглядеть следующим образом:
обратите внимание, что использование group by в вышеуказанном запросе не требуется, вы можете легко получить результаты, используя предложение where , как это сделано в первом запросе.
Запросы, которые вы написали, не имеют смысла и не будут разбираться в каких-либо других СУБД, о которых я знаю. MySQL расширяет стандартное использование SQL GROUP BY, чтобы список выбора мог ссылаться на неагрегированные столбцы, не названные в предложении GROUP BY., который может будь то благословение и проклятие.
Чтобы помочь, рассмотрите следующие примеры данных
Теперь, в вашем первом запросе (скорректированном с учетом данных выше)
Нам нужно сначала подумать о логическом порядке операций выше:
Итак, первое, на что нужно обратить внимание, это группа, так как class — это столбец группировки, который вы хотите вернуть по одной строке для каждой группы, так что у вас будет:
В заключении оговорки теперь указано, что он должен быть A, оставляя:
Теперь это причина, по которой большинство СУБД сбой, на данный момент вам нужно SELECT first_name , НО, у вас есть два доступных значения ( «Тест 1» и «Тест 2» ), но только один чтобы заполнить, и дали двигателю никаких инструкций, из которых один из двух выбрать. В документации указано, что движок свободен в выборе любого доступного значения, и этот порядок не повлияет на него, поэтому вы можете различаться для одного и того же запроса в зависимости от плана выполнения.
Пока вы не уверены в GROUP BY , я бы рекомендовал вам включить ONLY_FULL_GROUP_BY .
Вот почему вы получаете только одну строку, а не все ученики. Ответ о том, как получить всех учеников, довольно простой, просто используйте WHERE :
Если вам нужен одинаковый формат одной строки для каждого класса, но все учащиеся перечислены, то вам может понадобиться GROUP_CONCAT :
Наконец, вы должны иметь USING только с агрегатными функциями. Примером этого может быть попытка найти классы с 2 студентами в:
Источник
Некорректно работает GROUP BY во VIEW MYSQl. Лыжи не едут?
Имеем:
таблица со статусами лидов lsu ,допустим
Имеем VIEW lsu_desc, которая нам делает DESC
что нибудь типо
SELECT * FROM lsu ORDER by `update` DESC
Делаем еще один VIEW lsu_grouped чтобы получать последний статус
SELECT * FROM lsu_desc GROUP by `lead_id`
И на выходе не получаем,блин,последний статус. Получаем почему то первый
В то же время запрос
SELECT * FROM(SELECT * FROM lsu ORDER by `update` DESC) as inv GROUP by `lead_id`
работает корректно.
КАК ЖЕ ТАК ТО блин.
проект большой, тут не пишу всех join которые в реальных запросах
Мне нужны view для сущностей типо lead, чтобы не писать каждый раз в контроллерах запросы.
Что с mysql не так? Почему такая обработка из представления, а главное- что делать?
И именно когда из VIEW подтягиваешь. Если делать SELECT FROM SELECT таких проблем нет(
Прувы
- Вопрос задан более трёх лет назад
- 53 просмотра
Спасибо хабру,нашел(
Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
Тем не менее,какой механизм реализации посоветовали бы? Тригер на апдейт статуса в основной таблице?
Источник
MySQL: выбор ALL с GROUP BY не работает [дубликат]
В дополнение к другим правильным ответам вы можете рассмотреть возможность масштабирования ваших значений, чтобы избежать проблем с арифметикой с плавающей запятой.
Выражение 0.1 + 0.2 === 0.3 возвращает false в JavaScript, но, к счастью, целочисленная арифметика в плавающей запятой является точной, поэтому ошибки с десятичным представлением можно избежать путем масштабирования.
В качестве практического примера, чтобы избежать проблем с плавающей запятой, где точность имеет первостепенное значение, рекомендуется обрабатывать деньги как целое число, представляющее число центов: 2550 центов вместо 25.50 долларов.
18 ответов
Решение 1: удалить ONLY_FULL_GROUP_BY из консоли mysql
, вы можете прочитать здесь здесь
Решение 2: Удалить ONLY_FULL_GROUP_BY из phpmyadmin
- Открыть phpmyadmin & amp; выберите localhost
- Нажмите меню Переменные & amp; прокрутите вниз для режима sql
- Нажмите кнопку редактирования, чтобы изменить значения & amp; удалите ONLY_FULL_GROUP_BY & amp; нажмите «Сохранить».
В MySQL 5.7 и Ubuntu 16.04 отредактируйте файл mysql.cnf.
Включите sql_mode следующим образом и сохраните файл.
Обратите внимание, что , в моем случае я удалил режим STRICT_TRANS_TABLES и ONLY_FULL_GROUP_BY.
Сделав это, он сохранит конфигурацию режима навсегда. Иначе, если вы просто обновите @@ sql_mode через MySQL, потому что он перезагрузится при перезагрузке машины / службы.
После этого, чтобы модифицированная конфигурация приняла действие, перезапустите службу mysql:
Попробуйте получить доступ к mysql:
Если вы можете войти в систему и получить доступ к консоли MySQL, это нормально. Отлично!
Источник
