0184. Department Highest Salary¶
题目信息¶
- 题号:
部门工资最高的员工
- 难度:
Medium
- 主题:
Database、分组最大值、连接、并列保留
- 原题:
- 重点:
先按部门压缩出最高薪资,再用部门与薪资双条件回连员工以恢复全部并列者
题目重述¶
Employee(id, name, salary, departmentId) 保存员工,Department(id, name) 保存部门,
员工通过 departmentId 归属部门。
查询每个部门中薪资最高的所有员工,输出列依次命名为 Department、Employee、
Salary。同部门若有多人并列最高,必须全部保留;结果顺序任意。
自建示例¶
Department:
id | name
1 | Design
2 | Support
Employee:
id | name | salary | departmentId
1 | Asha | 8800 | 1
2 | Ben | 8800 | 1
3 | Cora | 7600 | 1
4 | Diego | 6900 | 2
5 | Elin | 7200 | 2
输出:
Department | Employee | Salary
Design | Asha | 8800
Design | Ben | 8800
Support | Elin | 7200
SQL 实现¶
SELECT
department.name AS Department,
employee.name AS Employee,
employee.salary AS Salary
FROM Employee AS employee
INNER JOIN Department AS department
ON department.id = employee.departmentId
INNER JOIN (
SELECT
departmentId,
MAX(salary) AS highest_salary
FROM Employee
GROUP BY departmentId
) AS highest
ON highest.departmentId = employee.departmentId
AND highest.highest_salary = employee.salary;
题解¶
全局最大值丢失了分区¶
直接计算 MAX(salary) 只能得到整张员工表的最高薪资,可能只属于一个部门。题目要求
每个部门独立竞争,所以比较状态必须包含 departmentId;不同部门即使薪资相同,也不
属于同一个排名集合。
按部门分组并计算 MAX(salary) 后,可以得到:
departmentId | highest_salary
1 | 8800
2 | 7200
这张派生表把每个部门的所有员工压缩成一个目标边界,删除了反复比较同部门薪资的工作。
聚合结果为什么还不是答案¶
分组表只有部门 id 和最高薪资,不再保留员工身份。直接在同一 GROUP BY 查询中选择
Employee.name,在严格 SQL 模式下不合法;即使某些数据库允许,也只会任意挑选组内
一行,既不能保证该员工薪资最高,也会丢掉并列者。
因此聚合只负责回答“每个部门的最高薪资是多少”,随后必须回到原始 Employee 行,
回答“哪些员工恰好达到这个薪资”。这体现了两种不同粒度:部门级边界与员工级输出。
双条件回连保留全部并列者¶
派生表 highest 与员工表的连接同时要求:
highest.departmentId = employee.departmentId
highest.highest_salary = employee.salary
第一个条件确保使用本部门边界;第二个条件只保留达到边界的员工。若只按薪资连接,一个 部门的最高薪资可能误匹配另一个部门的同薪员工;若只按部门连接,则该部门所有员工都会 留下。
连接不是“每组取一行”,而是保留所有满足等值条件的原始行。Design 部门的 Asha、Ben
都满足 salary = 8800,所以自然得到两行,无需 RANK 也无需特殊处理并列。
部门名称来自第三种粒度¶
Employee 只保存 departmentId,题目输出需要部门名称,因此再按
department.id = employee.departmentId 连接 Department。三部分各司其职:
分组派生表给出部门级最高薪资;
原始员工表恢复达到最高值的全部员工;
部门表把 id 映射为展示名称。
最终投影明确设置 Department、Employee、Salary 三个列名。部门若没有员工,
不会出现在最高薪资派生表中,也就没有“工资最高的员工”可输出。
具体走读¶
派生表先得到 Design 的 8800、Support 的 7200。回连 Employee 时,Asha、Ben 同时匹配
(departmentId=1, salary=8800);Cora 薪资不等于边界被删除。Support 中只有 Elin
匹配 7200。最后连接 Department,把 1、2 转成 Design、Support。
方案取舍与执行代价¶
也可以对员工使用 DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC)
并筛选名次 1;窗口法为每名员工保留部门内排名,适合还要输出更多名次的场景。本题只需
最高边界,分组 MAX 加回连更直接,因此选为主解。
逻辑上需要一次按部门聚合和一次等值连接。无索引时通常是线性扫描加哈希状态;
Employee(departmentId, salary) 复合索引可帮助分组或回连。查询不会为每名员工重复执行
一个相关最大值子查询,部门边界只计算一次。