MySQL基础练习题26-部分工资前三高的所有员工

目录

题目

准备数据

分析数据

总结

---

题目

一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。找出每个部门中 收入高的员工 。

准备数据

## 创建库
create database db;
use db;

## 创建employee表
Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int);

## 创建department表
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');

输入

employee表

department表

 

分析数据

在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的

在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工

第一步：使用窗口函数对每个部门的员工薪资进行排序

## 第一步：使用窗口函数对每个部门的员工薪资进行排序
select
    e.id,
    e.name as Employee,
    salary,
    departmentId,
    d.name as Department,
    dense_rank() over(partition by departmentId order by salary desc) rn
    from employee e join Department d on e.departmentId = d.id;

第二步：选出排名前三的员工 

## 第二步：选出排名前三的员工
with t1 as (
    select
        e.id,
        e.name as Employee,
        salary,
        departmentId,
        d.name as Department,
        dense_rank() over(partition by departmentId order by salary desc) rn
    from employee e join Department d on e.departmentId = d.id
)select
        Department,Employee ,salary
 from t1 where t1.rn <= 3;

总结

窗口排序一共有三种 

• row_number()    结果：1，2，3，4，5

• dense_rank()      结果：1，1，3，4，5

• rank()                  结果：1，1，2，2，3