GROUP BY, когда в предложении SELECT нет агрегированных функций

У меня есть таблица emp со следующей структурой и данными:

name   dept    salary
-----  -----   -----
Jack   a       2
Jill   a       1
Tom    b       2
Fred   b       1

Когда я выполняю следующий SQL:

SELECT * FROM emp GROUP BY dept

Получаю следующий результат:

name   dept    salary
-----  -----   -----
Jill   a       1
Fred   b       1

На каком основании сервер решил вернуть Джилл и Фреда и исключить Джека и Тома?

Я запускаю этот запрос в MySQL.

Примечание 1: Я знаю, что запрос не имеет смысла сам по себе. Я пытаюсь отладить проблему с сценарием "GROUP BY". Я пытаюсь понять поведение по умолчанию для этой цели.

Примечание 2: Я использую для написания предложения SELECT так же, как предложение GROUP BY (минус агрегированные поля). Когда я столкнулся с описанным выше поведением, я начал задаваться вопросом, могу ли я полагаться на это для сценариев, таких как: выберите строки из таблицы emp, где зарплата самая низкая/самая высокая в отделе. Например: SQL-запросы, подобные этому, работают на MySQL:

SELECT A.*, MIN(A.salary) AS min_salary FROM emp AS A GROUP BY A.dept

Я не нашел никакого материала, описывающего, почему такой SQL работает, что более важно, если я могу последовательно полагаться на такое поведение. Если это надежное поведение, я могу избежать таких запросов, как:

SELECT A.* FROM emp AS A WHERE A.salary = ( 
            SELECT MAX(B.salary) FROM emp B WHERE B.dept = A.dept)

Ответ 1

Прочитайте Документация по MySQL в этой конкретной точке.

В двух словах MySQL позволяет исключить некоторые столбцы из GROUP BY для целей производительности, однако это работает только , если опущенные столбцы имеют одинаковое значение (внутри группировки), в противном случае значение, возвращаемое запросом, действительно неопределенно, как это было правильно угадано другими в этом сообщении. Разумеется, добавление предложения ORDER BY не приведет к повторному введению какой-либо формы детерминированного поведения.

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

Выдержка из документации MySQL 5.0:

When using this feature, all rows in each group should have the same values
for the columns that are omitted from the GROUP BY part. The server is free
to return any value from the group, so the results are indeterminate unless
all values are the same.

Ответ 2

Это немного поздно, но я расскажу об этом для справок в будущем.

GROUP BY берет первую строку с дубликатом и отбрасывает любые строки, которые соответствуют после нее в наборе результатов. Итак, если у Джека и Тома есть тот же отдел, кто бы ни появился первым в обычном SELECT, будет результирующая строка в GROUP BY.

Если вы хотите контролировать то, что появляется первым в списке, вам нужно сделать ORDER BY. Однако SQL не позволяет ORDER BY перейти к GROUP BY, поскольку это вызовет исключение. Лучшим обходным решением этой проблемы является выполнение ORDER BY в подзапросе, а затем GROUP BY во внешнем запросе. Вот пример:

SELECT * FROM (SELECT * FROM emp ORDER BY name) as foo GROUP BY dept

Это лучшая техника, которую я нашел. Надеюсь, это поможет кому-то.

Ответ 3

Насколько я знаю, для ваших целей конкретные возвращаемые строки могут быть рассмотрены как случайные.

Заказ выполняется только после выполнения GROUP BY

Ответ 4

Вы можете поместить a:

SET sql_mode = 'ONLY_FULL_GROUP_BY'

перед тем, как ваш запрос будет применен к стандарту SQL GROUP BY SQL

Ответ 5

Я считаю, что лучше всего рассмотреть этот тип запроса без поддержки. В большинстве других систем баз данных вы не можете включать столбцы, которые не находятся в предложении GROUP BY или в агрегатной функции в предложениях HAVING, SELECT или ORDER BY.

Вместо этого учтите, что ваш запрос читает:

SELECT ANY(name), dept, ANY(salary)
FROM emp 
GROUP BY dept;

... так как это происходит.

Надеюсь, это поможет...

Ответ 6

Я думаю, что ANSI SQL требует, чтобы выбор включал только поля из предложения GROUP BY, а также агрегатные функции. Такое поведение MySQL выглядит как возвращающее некоторую строку, возможно, последнюю, прочитанную сервером, или любую строку, которую он имел под рукой, но не полагайтесь на это.

Ответ 7

Попробуйте использовать ORDER BY, чтобы выбрать нужную строку.

SELECT * FROM emp GROUP BY dept ORDER BY name ASC;

Вернет следующее:

name   dept    salary
-----  -----   -----
jack   a       2
fred   b       1

Ответ 8

Если вы группируете по отделам, имеет ли значение другие данные? Я знаю, что Sql Server даже не разрешит этот запрос. Если есть вероятность, что это звучит, возможно, могут быть другие проблемы.