编辑代码

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE  G0721A (
    project_id int,
    employee_id int
);

INSERT INTO G0721A(project_id, employee_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 4);

CREATE TABLE G0721B (
    employee_id int,
    name varchar(20),
    experience_years int
);

INSERT INTO G0721B (employee_id, name, experience_years)
VALUES (1, '张三', 3),
       (2, '李四', 2),
       (3, '王五', 3),
       (4, '马六', 2);

DESC G0721A;

# 查询出每个项目中经验最丰富(experience_years 最大)的员工
# 说明:员工 1 和 3 是 project_id 为 1 中 exprerience_years 最丰富的,
# 而 project_id 为 2 的项目,员工 id 为 1 的是 exprerience_years 最丰富

SELECT t.project_id, m.employee_id
FROM (
    SELECT project_id, max(e.experience_years) 'year'
    FROM G0721A p
    LEFT JOIN G0721B e
    ON p.employee_id = e.employee_id
    GROUP BY p.project_id
) t
LEFT JOIN (
    SELECT project_id, e.employee_id, e.experience_years
    FROM G0721A p
    LEFT JOIN G0721B e
    ON p.employee_id = e.employee_id
) m
ON t.project_id = m.project_id
AND m.experience_years = t.year;