原题说明
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 toEmployee
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)
1 | SELECT project_id, employee_id |
视频讲解
我们在Youtube上更新了视频讲解,欢迎关注!