- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
一,原题力扣链接
二,题干
项目表
Project
:+-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ (project_id, employee_id) 是这个表的主键(具有唯一值的列的组合) employee_id 是员工表 Employee 的外键(reference 列) 该表的每一行都表明具有 employee_id 的雇员正在处理具有 project_id 的项目。员工表
Employee
:+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id 是这个表的主键(具有唯一值的列) 该表的每一行都包含一名雇员的信息。编写解决方案,报告在每一个项目中 经验最丰富 的雇员是谁。如果出现经验年数相同的情况,请报告所有具有最大经验年数的员工。
返回结果表 无顺序要求 。
结果格式如下示例所示。
示例 1:
输入: Project 表: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee 表: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 3 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+---------------+ | project_id | employee_id | +-------------+---------------+ | 1 | 1 | | 1 | 3 | | 2 | 1 | +-------------+---------------+ 解释:employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中,employee_id 为 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', '3');
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2');
四,分析
题解:
表1:项目表
字段:项目id,员工id
表二:员工表
字段:员工id,员工姓名,员工工作年限
求:报告在每一个项目中 经验最丰富 的雇员是谁 允许并列 不要求排序
第一步:合并两个表 内连接的方式
select
p.project_id,p.employee_id,e.name,e.experience_years
,rank() over(partition by p.project_id order by e.experience_years desc) rn
from project p join employee e on p.employee_id=e.employee_id
第二步 分组排序 以项目分组,以项目工作年限排序 允许并列 用rank
第三步:取rn=1的行 映射指定的列 输出最后结果
五,SQL解答
with t1 as (
select
p.project_id,p.employee_id,e.name,e.experience_years
,rank() over(partition by p.project_id order by e.experience_years desc) rn
from project p join employee e on p.employee_id=e.employee_id
)
select project_id,employee_id from t1 where rn =1 ;
六,验证
七,知识点总结
- 经典面试题 分组求top1 且允许并列
- 内连接的运用
- 分组排序开窗函数的运用 rank开窗 允许并列
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用