经典sql题（五）查找各部门工资前三的员工

上一章讲解了如何使用四个不同的窗口函数：RANK(), DENSE_RANK(), ROW_NUMBER(), 和 PERCENT_RANK() 求成绩前三的学生，本文将展示不使用窗口函数的情况下通过 SQL 查询来查找每个部门中工资排名前三的员工。

示例数据

假设我们的 Employee 表和 Department 表有以下数据：

Employee 表

Id	Name	Salary	DepartmentId
1	Alice	3000	1
2	Bob	4000	1
3	Carol	2000	1
4	Dave	5000	2
5	Eve	4500	2
6	Frank	4200	2

Department 表

Id	Name
1	HR
2	Engineering

第一步：为每个员工的工资生成排名

我们首先需要为每个部门员工的工资生成一个排名，以便后续筛选。可以使用以下 SQL 查询：

SELECT 
    e.Id,
    e.Name AS Employee,
    e.Salary,
    e.DepartmentId,
    DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS Rank
FROM 
    Employee AS e;

结果（子查询结果）

Id	Employee	Salary	DepartmentId	Rank
2	Bob	4000	1	1
1	Alice	3000	1	2
3	Carol	2000	1	3
4	Dave	5000	2	1
5	Eve	4500	2	2
6	Frank	4200	2	3

解析

• DENSE_RANK() 函数为每个部门的员工生成工资排名，工资高的排名小。
• PARTITION BY e.DepartmentId 表示我们按照部门进行分区。
• ORDER BY e.Salary DESC 指定按照工资降序排列。

第二步：筛选出每个部门工资不高于第三的员工

接下来，我们将通过外层查询筛选出排名不高于 3 的记录：

SELECT 
    d.Name AS Department,
    ranked.Employee,
    ranked.Salary
FROM (
    SELECT 
        e.Name AS Employee,
        e.Salary,
        e.DepartmentId,
        DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS Rank
    FROM 
        Employee AS e
) AS ranked
JOIN Department AS d ON ranked.DepartmentId = d.Id
WHERE ranked.Rank <= 3
ORDER BY ranked.DepartmentId ASC, ranked.Salary DESC;

结果（最终结果）

Department	Employee	Salary
HR	Bob	4000
HR	Alice	3000
HR	Carol	2000
Engineering	Dave	5000
Engineering	Eve	4500
Engineering	Frank	4200

解析

• 在外层查询中，我们只保留排名不高于 3 的记录，这样就得到了每个部门中工资排名前几名的员工。
• 通过连接 Department 表获取部门名称，并按部门和工资排序。

完整步骤解析

1. 生成工资排名：

   • 查询：为每个部门的员工工资生成排名，以便后续筛选。
   • 结果表：显示员工的工资、部门及其对应的排名。

2. 筛选工资排名不高于第三的员工：

   • 查询：在生成的排名中筛选出排名不高于 3 的记录。
   • 最终结果：展示符合条件的员工和其所属部门。