1 题目描述
表: 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 是该表的主键 (具有唯一值的列)
该表的每一行都包含一名雇员的信息
编写一个解决方案来报告所有拥有最多员工的项目
以任意顺序 返回结果表
2 测试用例
输入:
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 名员工
3 解题思路
此处有坑:题目没有说明拥有最多员工的项目只有一个, 需要考虑多个项目都有最多员工的场景
- 使用
count(*) over (partition by project_id)分组统计每个项目的员工数量, 再通过倒序排序获取项目最多拥有的员工数量信息
select count(*) over (partition by project_id) as p_count
from Project
order by p_count desc
limit 0,1
查询结果
+-------+
|p_count|
+-------+
|3 |
+-------+
- 分组计算每个项目拥有的员工数量信息
select project_id, count(*) as num
from Project
group by project_id;
查询结果
+----------+---+
|project_id|num|
+----------+---+
|1 |3 |
|2 |2 |
+----------+---+
- 通过
having count(*)来查找出项目拥有的员工数量= 最多的员工数
select project_id
from Project
group by project_id
having count(*) = (select count(*) over (partition by project_id) as p_count
from Project
order by p_count desc
limit 0,1);
查询结果
+----------+
|project_id|
+----------+
|1 |
+----------+
