Leetcode SQL 刷题 185题

---

题目描述

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

---

一、分析思路

1.使用窗口排序函数
DDENSE_RANK() OVER(PARTITION BY 分组名称 ORDER BY 排序列)
首先使用DENSE_RANK() 按照降序查询出每个部门的员工工资的排序

SELECT Id,Name,Salary,DepartmentId, Dense_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) salary_rank
FROM Employee

然后连接部门表和员工工资排序表，加上筛选条件

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM  (SELECT Id,Name,Salary,DepartmentId, Dense_RANK() OVER(PARTITION BY DepartmentId ORDER BY                Salary DESC) salary_rank
        FROM Employee) AS e INNER JOIN Department AS d ON e.DepartmentId=d.Id
WHERE e.salary_rank<4

2.使用基本语法查询
首先连接同一部门的员工表1和员工表2，筛选出同一部门不小于你的工资的记录，然后比较同一部门不小于你的工资的员工的不重复计数工资额小于等3
注意：
WHERE e1.Salary<=e2.Salary 一定是小于等于，不然就会缺失自己的这条记录，例如张三是IT部工资最高的，如果是e1.Salary<e2.Salary ，则不会输出时不会返回张三这条记录。

COUNT(distinct e2.Salary)<=3，COUNT函数里面的也必须是不大于我的工资不超过三个。

SELECT e1.Name Name,e1.Salary,e1.DepartmentId
FROM Employee AS e1 INNER JOIN Employee AS e2 ON e1.DepartmentId=e2.DepartmentId 
WHERE e1.Salary<=e2.Salary
GROUP BY e1.Name 
HAVING COUNT(distinct e2.Salary)<=3

连接部门工资前三的员工表跟部门表

SELECT d.Name AS Department, e.NAME AS Employee, e.Salary
FROM
    (SELECT e1.Name Name,e1.Salary,e1.DepartmentId
    FROM Employee AS e1 INNER JOIN Employee AS e2 ON e1.DepartmentId=e2.DepartmentId 
    WHERE e1.Salary<=e2.Salary
    GROUP BY e1.Name 
    HAVING COUNT(distinct e2.Salary)<=3
    ) AS e INNER JOIN Department AS d ON  e.DepartmentId=d.Id