LC-mysql-185( Department Top Thr

本SQL问题是 #184. Department Highest Salary

解决本地主要是利用了mysql 实现rank

已下代码即可实现同DepartmentId内的根据Salary的rank

select l.*,
@rank := (case when @pre = l.Salary and @cur = l.DepartmentId then @rank when @cur != l.DepartmentId then 1  else @rank+1 end) as rank,
@cur :=  l.DepartmentId as cur,
@pre:=  l.Salary as pre
from (
  select * from Employee as e order by e.DepartmentId ,e.Salary desc
)l,(select @pre :=0 ,@cur := 0,@rank := 0)t

完整SQL如下:

select d.Name as Department,
      k.Name as Employee,
      k.Salary as Salary
from Department as d
join(
select ee.Name,ee.Salary,ee.DepartmentId from (
select l.*,
@rank := (case when @pre = l.Salary and @cur = l.DepartmentId then @rank when @cur != l.DepartmentId then 1  else @rank+1 end) as rank,
@cur :=  l.DepartmentId as cur,
@pre:=  l.Salary as pre
from (
  select * from Employee as e order by e.DepartmentId ,e.Salary desc
)l,(select @pre :=0 ,@cur := 0,@rank := 0)t)ee 
  where ee.rank < 4)k ON d.Id = k.DepartmentId