# Write your MySQL query statement below
1076. 项目员工II
表:Project
+-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ (project_id, employee_id) 是该表的主键(具有唯一值的列的组合)。 employee_id 是该表的外键(reference 列)。 该表的每一行都表明 employee_id 的雇员正在处理 Project 表中 project_id 的项目。
表:Employee
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id 是该表的主键(具有唯一值的列)。 该表的每一行都包含一名雇员的信息。
编写一个解决方案来报告所有拥有最多员工的 项目。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入: 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 | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+ | project_id | +-------------+ | 1 | +-------------+ 解释: 第一个项目有3名员工,第二个项目有2名员工。
原站题解
mysql 解法, 执行用时: 536 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:02:54
# Write your MySQL query statement below select project_id from project group by project_id having count(*) >=all (select count(*) amount from project group by project_id)
mysql 解法, 执行用时: 1148 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:02:42
select project_id from (select project_id, dense_rank() over(order by count(employee_id) desc) as ranking # 窗口函数适用范围广,在数据层面先构造再筛选 from project group by project_id) t where ranking = 1
mysql 解法, 执行用时: 573 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:02:26
select project_id from project group by project_id having count(employee_id ) >= all( select count(employee_id) from project group by project_id )