[Leetcode 1076] Project Employees II

原题说明

Table: Project

+————-+———+
| Column Name | Type    |
+————-+———+
| project_id  | int     |
| employee_id | int     |
+————-+———+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.

Table: Employee

+——————+———+
| Column Name      | Type    |
+——————+———+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+——————+———+
employee_id is the primary key of this table.

 

Write an SQL query that reports all the projects that have the most employees.

The query result format is in the following example:

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                |
+————-+——–+——————+
Result table:
+————-+
| project_id  |
+————-+
| 1           |
+————-+
The first project has 3 employees while the second one has 2.

解题思路

本题要查询所有项目(project)中拥有最多雇员的项目。需注意以下两点：

1. 为了要找出每个project_id中的雇员数量，需要对 project_id 使用 GROUP BY。
2. 然后对每个项目的雇员进行加和。因project表的主键是(project_id, employee_id)，所以不会存在重复条目。

另外一点是，需要先计算出最大雇员数，然后再查询雇员数与最大雇员数相等的项目即可。

示例代码 (mysql)

SELECT project_id 
FROM Project 
GROUP BY project_id
HAVING COUNT(*) = (
    SELECT COUNT(employee_id) AS cnt 
    FROM Project 
    GROUP BY project_id 
    ORDER BY cnt DESC 
    LIMIT 1);

视频讲解

我们在Youtube上更新了视频讲解，欢迎关注！