leetcode 185 MySQL
2021-04-22 本文已影响0人
七齐起器
SELECT
d.Name AS Department
,a.Name AS Employee
,a.Salary
FROM
(
SELECT
@r1:=(case when @DepartmentId=e.DepartmentId and @Salary>e.Salary then @r1+1
when @DepartmentId=e.DepartmentId and @Salary=e.Salary then @r1 else 1 end)rowNumber
,(@DepartmentId:=e.DepartmentId)
,(@Salary:=e.Salary)
,e.*
FROM
(
SELECT *
FROM Employee
GROUP BY Name ,DepartmentId ,Salary
ORDER BY DepartmentId, Salary DESC
)e,(select @r1:=0,@DepartmentId='',@Salary:=0)b
)a LEFT JOIN
Department d
ON a.DepartmentId = d.Id
WHERE a.rowNumber <= 3 AND d.Id is not null
ORDER BY d.Name