100天SQL面试刷题 Day004

先看题目自己试着解一下，有困难的同学可以先看考点解析试下，
最后再看SQL解答，有不同意见和解法的欢迎评论区留言交流

来源：大厂面试真题

题目：

表salary info包含以下字段
person id: 员工号(主键)
age: 年龄
department: 部门 (包含市场部和销售部两个部门)
salary: 薪资

(1)分别查询市场部和销售部工资最高的员工，并返回员工的department、 person id、age和salary。如果同部门工资最高的员工存在多个，则返回多个。

(2)分别查询市场部和销售部Top 5工资对应的所有员工，并返回员工的department、person id、age和salary。例如，市场部最高工资是10000，一共有3名员工的工资是10000，则应把这3名员工全部取出，以此类推。

(3)分别查询市场部和销售部年龄最大的10名员工，并返回员工的department、 person id、age和salary。如果同一部门年龄相同的员工存在多个，则按照person id从小到大排序来取。例如，年龄从大到小排序，发现排在第10的员工是50岁，而50岁的员工有多个，则再按person id升序排序，取到10名员工停止。
题目数据下载

结果输出

Q1执行结果

Q2执行结果

Q3执行结果

考点解析

对窗口函数中的三个序号函数的理解和用法。
ROW NUMBERO:排序，不会有重复的排序数值。对于相等的两个数字，排序序号不一致
RANK0是跳跃排序,即如果有两条记录重复接下来是第三级别如:1224会跳过3
DENSE RANKO是连续排序即如果有两条记录重复，接下来是第二级别如:1223

SQL代码

#第一题解法1：开窗rank
select
  a.department,a.person_id,a.age,a.salary from (
select * , rank() over
  (partition by 
department order by salary
  desc) as r
from train.salary_info) as a
where a.r =1;
　
# 第一题解法1：开窗dense_rank
select
  a.department,a.person_id,a.age,a.salary from (
select * , dense_rank() over
  (partition by 
department order by salary
  desc) as r
from train.salary_info) as a
where a.r =1;
　
# 第一题解法2：原表匹配最大salary
# goupby + max
  找到各部门最大的salary,原表判断=最大salary的数据
SELECT * FROM
  train.salary_info a
left join (
SELECT department,max(salary)
  as m FROM train.salary_info
group by department) tmp
on a.department =
  tmp.department
where salary = m;
　
# 第一题解法2：原表匹配最大salary
# order by + groupby找到各部门最大的salary
#limit 10000000000 是必须要加的，如果不加的话，数据不会先进行排序
select t.* from(
SELECT * FROM
  train.salary_info
order by salary desc limit
  10000000000) t 
group by t.department;
　
# 第一题解法3:关联子查询，
#先使用关联子查询把相同部门的员工归在一起，然后条件筛选出比各个员工工资高的员工
SELECT department, person_id,
  salary
FROM  train.salary_info t
WHERE (SELECT COUNT(DISTINCT
  e.salary)
       FROM 
  train.salary_info AS e
       WHERE e.department = t.department
       AND e.salary > t.salary) = 0;


#
  第二题：解法1.开窗函数-dense_rank
select
  t.department,t.person_id, t.age,t.salary from(
SELECT *,dense_rank() over(
partition by department
order by salary desc) as r
FROM train.salary_info) t
where t.r <= 5;
　
# 第二题：解法2.引入变量+if排序 (并列不占用)
set @rk=0,@d=null,@s=null;
select
  tmp.department,tmp.person_id, tmp.age,tmp.salary from (
select 
 t.department,t.person_id, t.age,t.salary,
 @rk :=
  if(@d=department,if(@s=salary,@rk,@rk+1),1) rk ,
 @d := department,
 @s :=salary
from train.salary_info t
order by t.department,t.salary
  desc) tmp
where tmp.rk <=5;


#第三题：解法1. row_number 排序
select department,person_id,age,salary from (
select *,
row_number() over(partition by department
 order by age desc,person_id) r
 from salary_info) tmp 
 where r <=10

#第三题 解法1.窗口函数lag前几
#考察窗口大小
--lag(col,n,default)用于统计窗口内往上第n个值。
--col:列名；n:往上第n行；default:往上第n行为NULL时候，取默认值,不指定则取NULL
select
  tmp.department,tmp.person_id,tmp.age,tmp.salary from(
select *,
lag(age,10) over(
partition by department
order by age desc,person_id
) a
from train.salary_info
) tmp
where a is null;