[Leetcode 1077] Project Employees III

原题说明

Table: Project

+————-+———+
| Column Name | Type    |
+————-+———+
| project_id  | int     |
| employee_id | int     |
+————-+———+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.

Table: Employee

+——————+———+
| Column Name      | Type    |
+——————+———+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+——————+———+
employee_id is the primary key of this table.

Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.

The query result format is in the following example:

Project table:
+————-+————-+
| project_id  | employee_id |
+————-+————-+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+————-+————-+
Employee table:
+————-+——–+——————+
| employee_id | name   | experience_years |
+————-+——–+——————+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 3                |
| 4           | Doe    | 2                |
+————-+——–+——————+
Result table:
+————-+—————+
| project_id  | employee_id   |
+————-+—————+
| 1           | 1             |
| 1           | 3             |
| 2           | 1             |
+————-+—————+
Both employees with id 1 and 3 have the most experience among the employees of the first project. For the second project, the employee with id 1 has the most experience.

解题思路

这道题可以先列出所有的project_id和对应的max(experience_years), 然后以(project_id, max(experience_years))为条件作filter即可。

示例代码 (mysql)

SELECT project_id, employee_id 
FROM Project
JOIN Employee
USING (employee_id)
WHERE (project_id, experience_years) IN
(
    SELECT project_id, MAX(experience_years)
    FROM Project
    JOIN Employee
    USING (employee_id)
    GROUP BY project_id
);

视频讲解

我们在Youtube上更新了视频讲解，欢迎关注！