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

select b.name as Department, a.name as Employee, a.Salary
from(
	select a.*, @rank:=case when @department=DepartmentId and @salary<>salary then @rank+1
	                        when @department=DepartmentId and @salary=salary then @rank
	                        else 1 end as rank,
	            @department:=DepartmentId,
	            @salary:=Salary
	from Employee as a, (select @rank:=0, @department:=0, @salary:=0) as b
	order by DepartmentId, Salary desc
	) as a
join Department as b on a.DepartmentId=b.Id
where a.rank<=3

###方法二

select c.Name as Department, a.Name as Employee, a.Salary
from Employee as a
join Employee as b on a.DepartmentId=b.DepartmentId and a.Salary<=b.Salary
join Department as c on a.DepartmentId=c.Id
group by a.Id #计算工资大于等于每个ID的数量
having count(distinct b.Salary)<=3 #因为a.Salary<=b.Salary，所以b.Salary的数量就是a中每个ID的排名
order by a.DepartmentId, a.Salary desc

如有错误，欢迎指正！