mysql - SQL:返回列上具有最大值的多行

Question

问题可以从here找到.

当每个部门有多个薪水最高的人时，下面的SQL命令输出预期的结果——输出所有薪水最高的员工:

select d.Name as Department, e.Name as Employee, e.Salary as Salary
from 
(select Name, Salary, DepartmentId
from Employee
having (Salary, DepartmentId) in (
    select max(Salary), DepartmentId
    from Employee
    group by DepartmentId)
)e, Department d
where e.DepartmentId = d.Id

但是，即使有多个员工的最高薪水相同，以下命令也只会输出一个员工:

select d.Name as Department, e.Name as Employee, e.Salary as Salary
from 
(select Name, Salary, DepartmentId
from Employee
group by DepartmentId
having Salary = max(Salary)
)e, Department d
where e.DepartmentId = d.Id

谁能给我解释一下为什么后一个不起作用？谢谢!

Answer 1

第二个查询不起作用的原因是因为无论部门如何，它都会获得最高薪水。

尽管第一个查询有效(它没有像它应该使用的标准 JOIN 语法)，但我这样做的方式是首先像这样获得每个部门的最高薪水:

SELECT departmentID, MAX(salary)
FROM employee
GROUP BY departmentID;

一旦你有了它，你就可以使用自连接来只选择具有该薪水和部门的行。如果薪水相同，这将包括多个人:

SELECT e.*
FROM employee e
JOIN(
   SELECT departmentID, MAX(salary) AS maxSalary
   FROM employee
   GROUP BY departmentID) tmp ON tmp.departmentID = e.departmentID AND tmp.maxSalary = e.salary

---

为了进一步解释，只考虑您拥有的内部子查询:

SELECT name, department, salary
FROM employee
GROUP BY department
HAVING salary = MAX(salary);

第一个问题是您选择的行在您的组中不一致。对于每个 department id，您有多个 name 值和多个 salary 值，这些值将在您进行分组时任意选择。在我的示例和第一个示例中，您可以看到分组显式提取每个部门中最高薪水的 (departmentID, salary) 对，而第二个示例则没有。

这是一个 SQL Fiddle示例以直观地查看您的第二个查询与我的查询之间的差异。