1077. 项目员工 III（难度：中等）

题目描述

（通过次数12,858 | 提交次数17,814，通过率72.18%）

项目表Project：
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键

员工表mployee：
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id 是这个表的主键

写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

查询结果格式在以下示例中：
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                |
+-------------+--------+------------------+

Result 表：
+-------------+---------------+
| project_id  | employee_id   |
+-------------+---------------+
| 1           | 1             |
| 1           | 3             |
| 2           | 1             |
+-------------+---------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/project-employees-iii

--测试数据
Create table If Not Exists Project (project_id int, employee_id int);
Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int);

insert into Project (project_id, employee_id) values ('1', '1');
insert into Project (project_id, employee_id) values ('1', '2');
insert into Project (project_id, employee_id) values ('1', '3');
insert into Project (project_id, employee_id) values ('2', '1');
insert into Project (project_id, employee_id) values ('2', '4');

insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3');
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2');
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '3');
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2');

解题思路

这是一道中等难度的题目，所以通过率比较高。

解题过程涉及到分析函数的使用。如果不使用分析函数，答题难度会高一些。

首先，通过Project与Employee的关联，获取每下项目下所有项目成员的经验年数；

然后，通过分析函数，计算出每个项目下每个项目成员的经验年数排名；

最后，取出排名为1的项目成员即可。

本题难度主要是在分析函数的使用上。

用来排名的分析函数，主要有如下3个：

* **row_number**：为每一行返回一个唯一的数字，排名相等则按随机顺序返回排名。

* **rank**：排名相等的情况下返回相同的排名，但排名结果会有断档。

* **dense_rank**：排名相等的情况下返回相同的排名，但排名结果不会有断档。

因为本题要求取最大经验年数，且经验年数相同时都返回，所以使用rank和dense_rank比较合适。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    c.project_id,
    c.employee_id
from
(
    select
        a.project_id,
        a.employee_id,
        rank() over(partition by a.project_id order by b.experience_years desc) rn
    from Project a
    inner join Employee b
    on a.employee_id = b.employee_id
)c
where rn = 1;