力扣题目跳转(1076. 项目员工II - 力扣(LeetCode))
表:
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名员工。
case 1 的建表语句。
Create table If Not Exists Project (project_id int, employee_id int)
Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int)
Truncate table Project
insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')
Truncate table Employee
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')
一 我们先通过分组,然后计算每个 id 组内的项目个数。
select project_id, count(*) as cnt from Project group by project_id
输出如下
二 在基础上使用 rank 排名函数,按照 cnt 降序排列,然后选择 rn = 1的即可。
select project_id from (with tmp as( select project_id, count(*) as cnt from Project group by project_id )select project_id, rank() over(order by cnt desc) as rn from tmp) as t where t.rn = 1;
输出如下
以上就是全部答案,如果对你有帮助请点个赞,谢谢。
来源:力扣(leecode)
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
转载请注明出处:
我会尽快把力扣上的所有数据库题目发出来。感兴趣的可以点个赞与关注。每天不定时跟新。