玖叶教程网

【力扣-570】

表：

问题：查询至少有5名下属的经理。

思路解析：

因为要求有5名下属的经理，并且员工不可能管理自己，所以需要进行自关联，并且为内连接（inner join）；然后按照b表的name分组（group by）统计a表员工的id数量即为员工数量；最后筛选（having）出员工数量大于等于5的即为所求的经理。

解答：

select
    b.name
from employee a
join employee b
on a.managerId=b.id
group by b.name
having count(distinct a.id)>=5

【力扣-184】

表：

问题：查询各个部门薪资最高的员工。

解题步骤：

1、要查询每个部门员工最高的薪资，先以员工表为左表部门表为右表进行左关联（left join）。

select 
  d.name depart_name,
  e.name emp_name,
  e.salary,
  rank() over(partition by d.name order by e.salary desc) as arank 
from employee e 
left join department d 
on e.departmentId=d.id

查询结果：

2、因为是每个部门，此时相当于以部门为一个组，然后找组内第一，所以用到窗口函数。同时因为第一名可能是并列，所以不能使用row_number() over()，要用rank() over()或dense_rank() over()，按照部门分组每个人的薪资降序排序生成新的字段。

select 
  d.name depart_name,
  e.name emp_name,
  e.salary,
  rank() over(partition by d.name order by e.salary desc) as arank 
from employee e 
left join department d 
on e.departmentId=d.id

查询结果：

3、最后将第二步得到的新表作为子表，然后对新生成的排序字段筛选，第一名的即为每个部门薪资最高的人员?。

select 
  a.depart_name 'Department',
  a.emp_name 'Employee',
  a.salary 'Salary'
from 
(
  select 
    d.name depart_name,
    e.name emp_name,
    e.salary,
    rank() over(partition by d.name order by e.salary desc) as arank 
  from employee e 
  left join department d 
  on e.departmentId=d.id 
) a 
where a.arank=1

查询结果：

好了，今天的分享就到这里，喜欢的话点个赞吧~

更多sql、excel、面试题、面经移步公众号：数据怎么玩