题60:
根据下面两个表写一个 SQL 查询语句,报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。
其中:
- Project表:(project_id, employee_id) 是这个表的主键,employee_id 是员工表 Employee 的外键;
- Employee表:employee_id 是这个表的主键。
解题思路:
(1)首先筛选出每个项目最长工龄;
select project_id , max(experience_years) maxy
from Project p1
join Employee e1 on p1.employee_id = e1.employee_id
group by project_id
(2)利用子查询提取每个项目的经验最丰富的员工,select * from ... where (project_id,experience_years) in ...
具体代码如下:
select project_id , e.employee_id
from Project p
join Employee e on p.employee_id = e.employee_id
where (project_id,experience_years) in (
select project_id , max(experience_years) maxy
from Project p1
join Employee e1 on p1.employee_id = e1.employee_id
group by project_id
)
;