如何查找各组得分最高的员工？以“部门工资最高的员工”为例。

预备知识

首先需要掌握的是查找所有人中得分最高的员工。

如果只查最高分的话，select max(score) from table就足够了。而如果还需要查出相关人员的信息，则需要进行一定的调整。

1. 子查询

一种查询方式是使用子查询，首先查找出所有人中最高的得分，然后扫描整个表，查找分数等于最高分的人。

select e.Name, e.Salary
from Employee e
where e.Salary = (
    select max(Salary)
    from Employee);

子查询一般可以转换为连接查询的形式。
并且由于子查询过程会生成和删除临时表，因此速度一般要比连接查询慢。

另外此处联系一下相关子查询和非相关子查询：

非相关子查询：子查询跟外部查询是无关的，执行过程是先执行子查询返回一个结果供外部查询使用。
相关子查询：子查询跟外部查询是相关的，执行过程是外部查询每查询一行，就要将该行的相关数据传递给子查询使用，即每一行都需要进行一次子查询，因此效率较低。

2. 连接查询

2.1. 连接临时表

首先查询出所有人中最高的得分，然后将结果作为临时表与原来的表进行连接，通过连接筛选出满足要求的结果。

select e.Name, e.Salary
from Employee e join (
    select max(Salary) as Salary
    from Employee) t on e.Salary = t.Salary;

2.2. 自连接

使用on控制连接条件，通过having计数得到最大值。

select e1.Name, e1.Salary
from Employee e1 join Employee e2 on e1.Salary <= e2.Salary
group by e1.Id
having count(distinct e2.Salary) = 1;

各组最高

文章中使用的表基于leetcode 184. 部门工资最高的员工。

首先Employee left join Department得到一个员工信息和部门信息关联的临时表。
然后group by DepartmentId分组，从每个组中找出max值。

错误示范1

1
2
3

select d.Name as Department, e.Name as Employee, max(Salary) as Salary
from Employee e left join Department d on e.DepartmentId = d.Id
group by e.DepartmentId;

上述代码错误的原因是，select中的max只挑选出了一个最大值，而忽略了可能有多个员工的薪资同为最大值的情况。
并且！select找出的max(Salary)并不是对应的行的Salary，而是整个组中的Salary
另外还存在的问题是，对于mysql 5.7及以上的版本，select的字段必须是被group by包含的字段，或是使用聚合函数。

错误示范2

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

上述代码看似解决了max只能去除最大值的问题，以及max(Salary)不是对应行的Salary的问题.
但是having子句中是不能出现非聚合函数中的属性名的，原因是group by分组之后得到的是组中的1条数据，所以having Salary只能获得这一条数据的Salary。
解决方法是不要在having子句中出现非聚合函数和非具体数据的其他字段。

正确答案

1. 子查询

首先找出每个分组及其最大值，然后判断表中每条数据的分组和Salary是否和这个相等。

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

2. 连接查询

2.1. 连接临时表

Employee和查询出来的每个分组的最大值连接。

select d.Name as Department, e.Name as Employee, e.Salary
from Employee e join Department d on e.DepartmentId = d.Id join (
    select DepartmentId, max(Salary) as Salary
    from Employee
    group by DepartmentId) t on e.DepartmentId = t.DepartmentId and e.Salary = t.Salary;

2.2. 自连接

Employee和自身连接，on的条件为部门相同且同时约束Salary的大小。

select d.Name as Department, e1.Name as Employee, e1.Salary
from Employee e1 join Department d on e1.DepartmentId = d.Id join Employee e2 on e1.DepartmentId = e2.DepartmentId and e1.Salary <= e2.Salary
group by e1.Id
having count(distinct e2.Salary) = 1;

扩展

从最高扩展到前N高/第N高如何计算，请参考：

从“各组得分最高的员工”到“各组得分前N高的员工“，以”部门公司前三高的所有员工“为例。