SQL——分组比较

废话不多说了，直接上题。

部门员工最高的工资

题目来源：184. 部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

分析：
Employee表中有Salary和DepartmentId,因此可以在该表中查出每个部门的最高薪水。

select DepartmentId, max(Salary)
    from Employee 
    group by
    DepartmentId)

然后需要得到的结果 Department，Employee和Salary在两个表中因此需要表连接，这里需要使用DepartmentId进行表连接，因为要求的是每个部门的最高薪水，因此Id不能为空，这里使用内连接，即两者均有的那部分。

select 
    d.Name as Department,
    e.Name as Employee,
    e.Salary as Salary
 from 
    Employee e
    inner join 
    Department d 
    on e.DepartmentId=d.id 

将查询出的结果使用where字句过滤，看是否在部门的最高薪水中

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

部门前三高的所有员工

题目来源：185. 部门工资前三高的所有员工

该题型为典型的分组排名问题，需要使用窗口函数来解决。
常用的窗口函数有：

• rank函数：如果有并列名次，会占用下一名次的位置。
• dense_rank函数：如果有并列名次的行，不占用下一名次的位置。
• row_number函数：不考虑并列名次的情况
  从题目的输出可以看出，这道题的并列名次并不占用下一名次的位置，因此我们选用dense_rank.

第一步：按照工资分组，并按照工资降序排列

select Name,Salary,DepartmentId,
    dense_rank() 
    over (partition by DepartmentId order by Salary desc) 
    as ranking
from Employee

第二步：将产生的临时表与Department连接，并获得排名小于等于3的信息

select d.Name as Department,a.Name as Employee,a.Salary as Salary from
(select Name,Salary,DepartmentId,
    dense_rank() 
    over (partition by DepartmentId order by Salary desc) 
    as ranking
from Employee)
as a
inner join Department d
on a.DepartmentId=d.Id
where a.ranking<=3;

参考链接：
图解SQL面试题：经典TOPN问题