- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用
目录
经典案例 分组求top1
一,原题力扣链接
二,题干
表:
Employee
+--------------+---------+ | 列名 | 类型 | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ 在 SQL 中,id是此表的主键。 departmentId 是 Department 表中 id 的外键(在 Pandas 中称为 join key)。 此表的每一行都表示员工的 id、姓名和工资。它还包含他们所在部门的 id。表:
Department
+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ 在 SQL 中,id 是此表的主键列。 此表的每一行都表示一个部门的 id 及其名称。查找出每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。示例 1:
输入: Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 解释:Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。
三,建表语句
Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int);
Create table If Not Exists Department (id int, name varchar(255));
Truncate table Employee;
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1');
insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2');
insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2');
insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1');
Truncate table Department;
insert into Department (id, name) values ('1', 'IT');
insert into Department (id, name) values ('2', 'Sales');
select * from Employee;
select * from Department;
四,分析
题解:
表1:员工表
字段:员工id,员工姓名,员工薪资,员工部门id
表2:部门表
字段:部门id,部门名称
求每个部门薪资最高的人经典案例 分组求top 1
第一步,用rank函数开窗 分组求第一
select
e.id,e.name,e.salary,e.departmentId,d.name as Department,
rank() over (partition by d.name order by salary desc ) rn
from employee e join department d on e.departmentId=d.id
第二步,取rn=1的值 然后取对应的列 并取别名即可
五,SQL解答
with t1 as (
select
e.id,e.name,e.salary,e.departmentId,d.name as Department,
rank() over (partition by d.name order by salary desc ) rn
from employee e join department d on e.departmentId=d.id
)
select Department,name as Employee, salary as Salary from t1 where rn=1;
六,验证
七,知识点总结
- 开窗函数的运用
- 并列第一用 rank函数
- 经典案例 分组求top1 要求并列~
- 学习:知识的初次邂逅
- 复习:知识的温故知新
- 练习:知识的实践应用