使用窗口函数和CTE实现各部门前三高薪资员工SQL查询

需求说明 #

需要从数据库的Salary和Department表中，查询出每个部门内薪资排名前三的员工（包含薪资并列的情况）。

表结构信息 #

Salary表 #

Id | Name  | Salary | DepartmentId
+----+-------+--------+-------------+
| 1 | Petr | 85000 | 1           |
| 2 | Ivan | 80000 | 2           |
| 3 | Alex | 60000 | 2           |
| 4 | Den  | 90000 | 1           |
| 5 | Bob  | 69000 | 1           |
| 6 | Kir  | 85000 | 1           |
| 7 | Mike | 76000 | 1           |

Department表 #

DepartmentId | DepartmentName
+-------------+----------------+
| 1           | IT             |
| 2           | Sales          |

期望查询结果 #

Department | Employee | Salary
+-----------+----------+--------+
| IT        | Den      | 90000  |
| IT        | Petr     | 85000  |
| IT        | Kir      | 85000  |
| IT        | Mike     | 76000  |
| Sales     | Ivan     | 80000  |
| Sales     | Alex     | 60000  |

现有子查询写法 #

SELECT d.name as Department,s1.name as Employee,s1.salary
FROM salary s1 
  left join department d on s1.departmentid=d.id
WHERE (SELECT COUNT(DISTINCT s2.salary) 
       from Department d2
       WHERE s2.salary>s1.salary and s2.departmentid=s1.departmentid)<3
ORDER BY d.name,s1.salary DESC;

窗口函数+CTE解决方案 #

使用DENSE_RANK()窗口函数实现密集排名，配合CTE可以更清晰地完成需求：

WITH RankedSalaries AS (
    SELECT 
        s.Name AS Employee,
        s.Salary,
        d.Name AS Department,
        -- 按部门分组，薪资降序做密集排名，相同薪资排名一致
        DENSE_RANK() OVER (PARTITION BY s.DepartmentId ORDER BY s.Salary DESC) AS SalaryRank
    FROM Salary s
    INNER JOIN Department d ON s.DepartmentId = d.DepartmentId
)
-- 筛选排名前三的员工
SELECT Department, Employee, Salary
FROM RankedSalaries
WHERE SalaryRank <= 3
ORDER BY Department, Salary DESC;

说明 #

• DENSE_RANK()会为相同薪资的员工分配相同排名，且排名连续（比如90000排1，两个85000都排2，76000排3），符合需求中包含并列的场景。
• CTE RankedSalaries先完成排名计算，再筛选结果，逻辑更直观易读。

内容的提问来源于stack exchange，提问作者Felipe Felix