MySQL多表关联,内连接外连接查询应用

1.查询部门里部门中的职位最高薪水
2.查部门中的最高薪水和最低薪水的薪资
3.找各部门比管理者工资高的人
4.找部门里高于平均工资的一批人

# 、查询每个部门中每个职位的最高薪水。
select dp.depName, bf.staffWork,bf.salary from baijieStaff as bf inner join baijiedep as dp
  on bf.staffDepID=dp.depID order by salary desc;
# 、查询出比本部门最高工资低的员工姓名，薪水，部门号和本部门最高薪资
select staffDepId as '部门id' ,staffName,salary,max(salary) over
    (partition by staffDepId order by salary desc) as '部门最高薪资',
    min(salary) over(partition by staffDepId order by salary asc) as '部门最低工资'
from baijieStaff;
# 、找出中那些工资高于他们所在部门的管理者工资的员工
#内连接做法
select bf.staffLead, bf.staffName,bf.salary  from baijieStaff as bf inner join
  (select staffLead,staffName,salary from baijieStaff
  where staffName='长贵' or staffName='赵四' or staffName='刘大脑袋') as bf1
on bf.staffLead=bf1.staffLead where bf.salary>bf1.salary;

# 丶找出中那些工资高于他们所在部门普通员工平均工资的员
#外连接
select bf.staffLead,bf.staffName,bf.salary,bf2.pj as '平均工资' from baijieStaff as bf left outer join
(select *,avg(salary)over(partition by staffLead) as pj from baijieStaff) as bf2
on bf.staffID=bf2.staffID where bf.salary>bf2.pj;