1076. 项目员工II

Leetcode题目:Limit 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 是该表的主键(具有唯一值的列)。
该表的每一行都包含一名雇员的信息。

编写一个解决方案来报告所有拥有最多员工的 项目。

以 任意顺序 返回结果表。

返回结果格式如下所示。

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

解题思路

LIMIT 1

COUNT()=(SELECT…),最后只能返回一条记录才能作大小判断,所以得LIMIT。

# Write your MySQL query statement below
SELECT project_id FROM Project 
GROUP BY project_id
HAVING COUNT(employee_id)=(
  SELECT COUNT(employee_id)
  FROM Project 
  GROUP BY project_id
  ORDER BY COUNT(employee_id) DESC
  LIMIT 1
)
Subscribe
提醒
0 评论
Inline Feedbacks
View all comments
0
在此留下你的评论x