185. 部门工资前三高的所有员工

问题背景

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

解决方案

第一种方法，使用dense_rank()

select 
    Department.Name as Department ,tempa.Name as Employee,tempa.Salary
from
    (select
        e.Id,
        e.Name,
        e.Salary,
        e.DepartmentId,
        dense_rank() over (partition by e.DepartmentId order by e.Salary desc) as num
    FROM
        Employee e ) tempa
    join
    Department  
    on Department.Id = tempa.DepartmentId
where
    tempa.num <=

image.png

需要注意的就是 dense_rank() rank() row_number() 这三个函数的区别。

image.png

第二种解决办法

select 
    d.Name as Department,e.Name as Employee ,e.Salary
from
    Employee e
    join 
    Department d 
    on e.DepartmentId = d.Id
where
    (select count(distinct(e2.Salary))
     from Employee e2
     where
        e.DepartmentId =  e2.DepartmentId
        and e2.Salary >= e.Salary
    )<=3

image.png

对于自连接的条件，需要特别注意。

1030 ms, 在所有MySQL提交中击败了91.48%的用户。个人觉得刷SQL题，能不用函数就不要用函数，基本的语法明明能做的。受之前某一题的某个大佬启发，对于这种分组内取前几名的问题，可以先group by然后用having count()来筛选，比如这题，找每个部门的工资前三名，那么先在子查询中用Employee和自己做连接，连接条件是【部门相同但是工资比我高】，那么接下来按照having count(Salary) <= 2来筛选的原理是：如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，这样内层查询可以查询出所有符合要求的员工ID，接下来外层查询就简单了。

image.png

第三种解决办法，利用变量

select
    Department.Name as Department,
    temp.Name as Employee,
    temp.Salary as Salary
from
    (select 
        Id,Name,Salary,DepartmentId,
        @num := if(@dep = DepartmentId, if( @sal =Salary,@num,@num := @num+1) ,@num :=1 ) as  num,
        @sal := Salary,
        @dep := DepartmentId
    from
        Employee,(select @dep := null ,@sal := null, @num := 0) e
    order by DepartmentId,Salary desc) temp
    join
    Department
    on  temp.DepartmentId = Department.Id
where
    num <=3

image.png