题目:
写 一个 SQL 查询语句,报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。
查询结果格式在以下示例中:
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中,employee_id 为 1 的员工拥有最丰富的经验。
解题思路:
①先以project_id进行分组,再按experience_year进行降序排序。
1 select project_id,a.employee_id,name,experience_years, dense_rank() over(partition by project_id order by experience_years desc) as rnk 2 from project a 3 join employee b 4 on a.employee_id = b.employee_id
②选出rnk = 1的数据即可。
1 select project_id,employee_id 2 from ( 3 select project_id,a.employee_id,name,experience_years, dense_rank() over(partition by project_id order by experience_years desc) as rnk 4 from project_1075 a 5 join employee_1075 b 6 on a.employee_id = b.employee_id 7 ) as temp 8 where rnk = 1;标签:rnk,project,1077,experience,years,力扣,MySQL,employee,id From: https://www.cnblogs.com/liu-myu/p/17299872.html