# Write your MySQL query statement below
1077. 项目员工 III
项目表 Project
:
+-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ (project_id, employee_id) 是这个表的主键(具有唯一值的列的组合) employee_id 是员工表 Employee 的外键(reference 列) 该表的每一行都表明具有 employee_id 的雇员正在处理具有 project_id 的项目。
员工表 Employee
:
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id 是这个表的主键(具有唯一值的列) 该表的每一行都包含一名雇员的信息。
编写解决方案,报告在每一个项目中 经验最丰富 的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。
返回结果表 无顺序要求 。
结果格式如下示例所示。
示例 1:
输入: Project 表: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee 表: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 3 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+---------------+ | project_id | employee_id | +-------------+---------------+ | 1 | 1 | | 1 | 3 | | 2 | 1 | +-------------+---------------+ 解释:employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中,employee_id 为 1 的员工拥有最丰富的经验。
原站题解
mysql 解法, 执行用时: 365 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 19:55:27
# 链接2表 with t as (select a.project_id, a.employee_id, b.experience_years from project a join employee b using(employee_id)) # 窗口函数 select project_id,employee_id from (select project_id, employee_id, dense_rank() over(partition by project_id order by experience_years desc) as rnk from t) tmp where rnk =1
mysql 解法, 执行用时: 483 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 19:54:57
# 链接2表 with t as (select a.project_id, a.employee_id, b.experience_years from project a join employee b using(employee_id)) # 子查询匹配 select project_id,employee_id from t where (project_id, experience_years) in (select project_id, max(experience_years) from t group by 1)
mysql 解法, 执行用时: 378 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 19:53:41
# Write your MySQL query statement below 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 );