力扣 185.部门工资前三高的所有员工

最新推荐文章于 2024-10-13 15:10:00 发布

奋斗哼哼

最新推荐文章于 2024-10-13 15:10:00 发布

阅读量94

点赞数

文章标签： leetcode sql 数据库

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130326021

版权

数据准备

drop table Employee;
drop table Department;

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', '85000', '1');
insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1');
insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1');
insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1');
insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '1');
Truncate table Department;
insert into Department (id, name) values ('1', 'IT');
insert into Department (id, name) values ('2', 'Sales');

输入

在这里插入图片描述

输出

with t1 as (
    select e.id, e.name, e.salary, e.departmentId, d.name as dep_name
    from Employee e, department d
    where e.departmentId=d.id
),t2 as (
    select * ,
       dense_rank() over (partition by dep_name order by salary desc ) rn1
    from t1
)
select dep_name as Department,name Employee,salary
from t2
where rn1<=3
;