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)
Truncate table Project
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')
Truncate table Employee
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')
写 一个 SQL 查询语句,报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。
查询结果格式在以下示例中:
SELECT
p.project_id,
p.employee_id
FROM
Project AS p
INNER JOIN Employee AS e
ON p.employee_id = e.employee_id
WHERE (p.project_id, e.experience_years) IN (
SELECT
p.project_id,
MAX(e.experience_years)
FROM
Project AS p
INNER JOIN Employee AS e
ON p.employee_id = e.employee_id
GROUP BY p.project_id
);