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
上一篇下一篇

猜你喜欢

热点阅读