leetcode 1077. 项目员工 III----dense_rank()

项目员工iii
题目分析：题目给了员工的编号，项目的编号，要求找出项目中经验最多的人，这里面注意的是，最多的人可能不止一个。按照项目分类，使用dense_rank()排序即可。
先连接两张表

select P.project_id,P.employee_id,E.experience_years from Project P inner join Employee E on P.employee_id = E.employee_id

再使用dense_rank()编号

select *,dense_rank() over(partition by project_id order by experience_years desc) dk from (
select P.project_id,P.employee_id,E.experience_years from Project P inner join Employee E on P.employee_id = E.employee_id) T1

最后筛选出项目名称和对应的id

select project_id,employee_id from (
select *,dense_rank() over(partition by project_id order by experience_years desc) dk from (
select P.project_id,P.employee_id,E.experience_years from Project P inner join Employee E on P.employee_id = E.employee_id) T1) T2 where dk=1

提交结果如下：