列表

详情


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名员工。

原站题解

去查看

上次编辑到这里,代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

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  
   )

上一题