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;
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;