Question

1076. 项目员工 II

English Version

题目描述

表：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
SELECT project_id
FROM Project
GROUP BY 1
HAVING
  COUNT(1) >= all(
    SELECT COUNT(1)
    FROM Project
    GROUP BY project_id
  );

方法二

# Write your MySQL query statement below
WITH
  T AS (
    SELECT
      project_id,
      RANK() OVER (ORDER BY COUNT(employee_id) DESC) AS rk
    FROM Project
    GROUP BY 1
  )
SELECT project_id
FROM T
WHERE rk = 1;