每个部门获得前三高工资的所有员工

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

mysql中：

select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee as e left join Department as d
on e.DepartmentId = d.Id
where e.Id in
(
select e1.Id
from Employee as e1 left join Employee as e2
on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
group by e1.Id
having count(distinct e2.Salary) <= 2
)
and e.DepartmentId in (select Id from Department)
order by d.Id asc,e.Salary desc

Hive中：

Select d.Name as Department ,e1.Name as Employee,e1.Salary
From
(Select * from
(select Id,Name,Salary，dense_rank() over (partition by DepartmentId order by Salary desc )rank
from Employee) as t
where rank<=3) as e1
inner join Department as d
on e1.DepartmentId=d.Id