LeetCode_sql_day02(185部门工资前三高的员工、176第二高的薪水)

185部门工资前三高的员工

描述：

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

数据准备：

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')

分析：

先将两个表进行连接，同时可以根据部门进行分组，用窗口函数来为各部门薪资进行排序，最后通过子查询查找各部门薪资排名前三高的员工。

①连接

②分组 并且对各部门薪资进行排序 然后过滤

代码：

select Department,Employee,Salary from(
select department.name         Department,
       employee.name           Employee,
       salary                  Salary,
       dense_rank() over (partition by departmentId order by salary desc ) r
from employee
         join department on employee.departmentId = department.id ) rn
where r <= 3;

总结：

1.三个排名函数区别：

①row_number()

        select dname,salary,row_number() over(partition by departmentId order by salary)

        等级  from  employee;-- 【1，2，3，4】

②rank()

        select dname,salary,rank() over(partition by departmentId order by salary) 等级 from         employee; -- 【1，2，2，4】

③dense_rank()

        select dname,salary,dense_rank() over(partition by departmentId order by salary) 等级         from employee; -- 【1，2，2，3】

2.最后的子查询用来过滤排名

176第二高的薪水

描述：

查询并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

数据准备：

Create table If Not Exists Employee (id int, salary int)

Truncate table Employee

insert into Employee (id, salary) values ('1', '100')

insert into Employee (id, salary) values ('2', '200')

insert into Employee (id, salary) values ('3', '300')

分析：

法一：

①先根据薪资进行降序排序，用limit函数取第二个即可，注意要去重

②再使用ifnull()函数进行判断，没有第二高的薪资就赋值为null

法二：

①先对工资进行分组

②根据工资降序排序，

使用lead()窗口函数找到相较于本行的下一行工资

③取此时本列的第一个数据即第二高工资

代码：

法一：
select ifnull(
        (select distinct salary 
         from Employee 
         order by salary desc 
         limit 1,1), null) SecondHighestSalary;
法二：
select lead(salary) over ( order by salary desc ) SecondHighestSalary
from employee
group by salary
limit 1;

总结：

①ifnull()函数:

判断空，给出返回值

②limit函数:

分页查询 limit m,n 

m表示从第几个索引开始，默认为0  计算：（当前页码-1）*每页要显示的数据

n表示查询多少个数据  

③partition by 和group by区别

partition by用于窗口函数中，计算每个分组的聚合值并保留每行的详细信息；

group by 则直接对原始数据进行分组并计算每个分组的聚合值