项目员工 III
写 一个 SQL 查询语句,=报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。=
查询结果格式在以下示例中:
方法一:
select
t.project_id,
t.employee_id
from
(select
p.project_id,
p.employee_id,
rank() over(partition by p.project_id order by e.experience_years desc) as rk
from
Project as p left join Employee as e on e.employee_id=p.employee_id) as t
where
rk = 1
方法二:
分组求最大值,在in()查询