1 题目描述
项目表 Project:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) 是这个表的主键 (具有唯一值的列的组合)
employee_id 是员工表 Employee 的外键 (reference 列)
该表的每一行都表明具有 employee_id 的雇员正在处理具有 project_id 的项目
员工表 Employee:
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id 是这个表的主键 (具有唯一值的列)
该表的每一行都包含一名雇员的信息
编写解决方案, 报告在每一个项目中经验最丰富 的雇员是谁. 如果出现经验年数相同的情况, 请报告所有具有最大经验年数的员工
返回结果表无顺序要求
2 测试用例
输入:
Project 表:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+
Employee 表:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
+-------------+--------+------------------+
输出:
+-------------+---------------+
| project_id | employee_id |
+-------------+---------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
+-------------+---------------+
解释: employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验. 在 project_id 为 2 的项目中, employee_id 为 1 的员工拥有最丰富的经验
3 解题思路
- 将表
Project和Employee关联起来, 按照project_id分组对员工的工作年限进行排名
select p.project_id,
p.employee_id,
e.experience_years,
rank() over (partition by p.project_id order by e.experience_years desc) as rn
from Project as p
inner join Employee as e
on p.employee_id = e.employee_id
查询结果
+----------+-----------+----------------+--+
|project_id|employee_id|experience_years|rn|
+----------+-----------+----------------+--+
|1 |1 |3 |1 |
|1 |3 |3 |1 |
|1 |2 |2 |3 |
|2 |1 |3 |1 |
|2 |4 |2 |2 |
+----------+-----------+----------------+--+
- 对上述结果进行排名过滤
s.rn = 1, 查找出每个项目工作年限最多的员工信息
select s.project_id,
s.employee_id
from (select p.project_id,
p.employee_id,
e.experience_years,
rank() over (partition by p.project_id order by e.experience_years desc) as rn
from Project as p
inner join Employee as e
on p.employee_id = e.employee_id) as s
where s.rn = 1;
查询结果
+----------+-----------+
|project_id|employee_id|
+----------+-----------+
|1 |1 |
|1 |3 |
|2 |1 |
+----------+-----------+
