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

1、题目描述

来源：力扣（LeetCode）

2、解题思路

思路一

1# 创建子查询，Employee，e，e12表联查，count计算所有大于当前e.Salary的数量，即为当前e.Salary的排名（从0开始），令其<3,即为前3
select count(distinct e1.Salary) from Employee e1
where e1.Salary>e.Salary and e1.DepartmentId=e.DepartmentId
2# 2表联查Employee e join Department d

思路二

1# 在表Employee增加一列rank作为排名：
@i作为排名变量，@j:=DepartmentId，@k:=Salary
使用case when else end，当DepartmentId相同则计算Salary排名，不相同则初始化排名为1else 1；参考178.分数排名
2# 与Department2表联查，增加条件e.rank<4

3、提交记录

思路一

select d.Name as Department,e.Name as Employee,e.Salary
from Employee e join Department d
on e.DepartmentId=d.Id
where 3>
(select count(distinct e1.Salary)
from Employee e1
where e1.Salary>e.Salary and e1.DepartmentId=e.DepartmentId)
order by Department,Salary desc;

思路二

select d.Name as Department,e.Name as Employee,e.Salary
from Department d join (SELECT Name,Salary,@i:=case when @j=DepartmentId then @i+(@k<>(@k:=Salary)) else 1 end as rank,@j:=DepartmentId as DepartmentId
FROM employee,(select @i:=0,@j:=0,@k:=0) a
ORDER BY DepartmentId,salary desc)as e
on e.DepartmentId=d.Id and e.rank<4