SQL排序不遵循group by语句，始终使用主键 [英] SQL sorting does not follow group by statement, always uses primary key

问题描述

我有一个SQL数据库，该数据库具有名为staff的表，该表具有以下列:

I have a SQL database with a table called staff, having following columns:

workerID (Prim.key), name, department, salary

我应该找到每个部门薪水最高的工人，并使用以下陈述:

I am supposed to find the workers with the highest salary per department and used the following statement:

select staff.workerID, staff.name, staff.department, max(staff.salary) AS biggest
from staff
group by staff.department

我从每个部门看到一个工人，但是他们并不是薪水最高的工人，但是即使工人没有得到该薪水，也显示了最高的薪水值.

I get one worker shown from each department, but they are NOT the workers with the highest salary, BUT the biggest salary value is shown, even though the worker does not get that salary.

显示的人是每个部门中具有最低" workerID的工人.

The person shown is the worker with the "lowest" workerID per department.

因此，即使在group by语句中未提及，使用主键仍会进行一些排序.

So, there is some sorting going on using the primary key, even though it is not mentioned in the group by statement.

有人可以解释一下，这是怎么回事，以及如何正确排序.

Can someone explain, what is going on and maybe how to sort correctly.

推荐答案

对正在发生的事情的解释:

您正在staff.department上执行GROUP BY，但是您的SELECT列表包含2个非分组列staff.workerID, staff.name.在标准sql中，这是一个语法错误，但是MySql允许这样做，因此查询编写者必须确保自己处理此类情况.

You are performing a GROUP BY on staff.department, however your SELECT list contains 2 non-grouping columns staff.workerID, staff.name. In standard sql this is a syntax error, however MySql allows it so the query writers have to make sure that they handle such situations themselves.

参考: http://dev.mysql. com/doc/refman/5.0/en/group-by-handling.html

在标准SQL中，包含GROUP BY子句的查询不能引用选择列表中未在GROUP BY子句中命名的未聚合列.

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

MySQL扩展了GROUP BY的使用，以便选择列表可以引用未在GROUP BY子句中命名的非聚合列.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

服务器可以从每个组中自由选择任何值，因此，除非它们相同，否则选择的值是不确定的.

从MySQL 5.1开始，可以通过在sql_mode中设置ONLY_FULL_GROUP_BY标志来禁用非标准功能:

Starting with MySQL 5.1 the non-standard feature can be disabled by setting the ONLY_FULL_GROUP_BY flag in sql_mode: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_only_full_group_by

如何修复:

select staff.workerID, staff.name, staff.department, staff.salary
from staff
join (
  select staff.department, max(staff.salary) AS biggest
  from staff
  group by staff.department
) t
on t.department = staff.department and t.biggest = staff.salary

在内部查询中，使用GROUP BY获取部门及其最高薪水.然后在外部查询中将这些结果与主表连接起来，这将为您提供所需的结果.

In the inner query, fetch department and its highest salary using GROUP BY. Then in the outer query join those results with the main table which would give you the desired results.

这篇关于SQL排序不遵循group by语句，始终使用主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！