【SQL】部门最工资最高的员工&&部门工资前三高的员工&&部门工资前K高的员工

墨蓝 2022-12-16 09:17 172阅读 0赞

> 本篇文章主要阐述本人对于LeetCode上，部门最工资最高的员工&&部门工资前三高的员工&&部门工资前K高的员工的一个统一的解题思路，如有错误欢迎指出

[LeetCode185][]  
[LeetCode184][]

## 解题思路 ##

这个题目我首先是想到了组内排序,然后就考虑使用自定义用户变量的方式。我借鉴了[178. 分数排名 ][178. _]这个题目,本质就是把同一部门的员工连在一起,然后工资从大到小排序然后使用自定义变量动态的排名,最后筛选出第K名

注意order by 一般是在 select之后进行的

## 解题分三步 ##

1.  准备好按照部门与工资排序的表其中工资为 desc

SELECT d.Name AS Department, e.Name AS Employee ,e.Salary  AS Salary ,DepartmentId
    FROM Employee e
    JOIN Department d
    ON e.DepartmentId =d.Id
    ORDER BY d.Id ,e.Salary DESC

1.  将准备好的表与自定义变量相连接`@rank`表示排名,`@pre_dept`表示部门,`@pre_score`表示工资

(SELECT @rank:=0,@pre_dept:=NULL,@pre_score:=NULL) init

1.  筛选出排名为 1 的表

WHERE te.rk=1

![在这里插入图片描述][20201019191845203.png_pic_center]

## 完整代码 ##

### 自定义变量 ###

# Write your MySQL query statement below
    SELECT Department, Employee , Salary
    FROM (
    SELECT Department, Employee , Salary ,
    CASE
         WHEN @pre_dept=DepartmentId AND @pre_score=Salary THEN  @rank:= @rank+0
         WHEN @pre_dept=DepartmentId AND @pre_score<>Salary THEN  @rank:= @rank+1
         WHEN @pre_dept:=DepartmentId THEN @rank:=1
         END rk, @pre_score:=Salary AS t
    FROM (
    SELECT d.Name AS Department, e.Name AS Employee ,e.Salary  AS Salary ,DepartmentId
    FROM Employee e
    JOIN Department d
    ON e.DepartmentId =d.Id
    ORDER BY d.Id ,e.Salary DESC
    ) ti, (SELECT @rank:=0,@pre_dept:=NULL,@pre_score:=NULL) init
    ) te
    WHERE te.rk<=k

### 窗口函数 ###

select Department,Employee,Salary
    from (
    SELECT d.Name AS Department, e.Name AS Employee ,e.Salary  AS Salary,DENSE_RANK() over(partition by  d.Id order by Salary desc  ) as rk
    FROM Employee e
    JOIN Department d
    ON e.DepartmentId =d.Id
    ) t
    where t.rk<=3
    order by Department
    ```

[LeetCode185]: https://leetcode-cn.com/problems/department-top-three-salaries/
[LeetCode184]: https://leetcode-cn.com/problems/department-highest-salary/
[178. _]: https://leetcode-cn.com/problems/rank-scores/
[20201019191845203.png_pic_center]: /images/20221123/776434d8df64499b98dbab10be12e1c0.png