题解 | #获取每个部门中当前员工薪水最高的相关信息#

'''方法一：采用子查询，查出最大工资后匹配相应的员工和部门信息'''
-- **子查询 使用all比对求最大工资**
with a as (select d.emp_no,dept_no,salary 
           from dept_emp d
           join salaries s
           on d.emp_no = s.emp_no) -- 建一个临时表，简化表连接
SELECT  dept_no
       ,d.emp_no
       ,salary maxSalary
from dept_emp d
join salaries s
on d.emp_no = s.emp_no
where s.salary >= all (SELECT salary from a  -- 此处每个部门只会返回一条
                       where d.dept_no = a.dept_no)
order by dept_no

注意：子查询使用all一定程度上等于使用max，但是这道题如果单纯的用最大工资进行匹配会出现问题
那就是存在*某部门的最大工资*与**其*他部门的某个员工的工资***一样，导致输出一个部门有两条记录
/* where s.salary in (SELECT max(salary) from a
                       where d.dept_no = a.dept_no) */ -- 错误，因为其他部门也有同工资的
这个问题在下边的分组求最大工资中可能也会出现
-- 只一个值查询错误
/*with a as (select d.emp_no,dept_no,salary 
           from dept_emp d
           join salaries s
           on d.emp_no = s.emp_no)
SELECT  dept_no
       ,d.emp_no
       ,salary maxSalary
from dept_emp d
join salaries s
on d.emp_no = s.emp_no
where s.salary in (SELECT max(salary) from a   -- 错误，因为其他部门也有同工资的
                   group by dept_no)
order by dept_no*/

解决方法就是使用列表（多个字段）匹配
如下：
**-- 子查询，使用分组求最大工资** 使用groupby时注意select中只能出现聚合字段和聚合函数哦
-- 需要部门id和工资一起比对
with a as (select d.emp_no,dept_no,salary 
           from dept_emp d
           join salaries s
           on d.emp_no = s.emp_no)
SELECT  dept_no
       ,d.emp_no
       ,salary maxSalary
from dept_emp d
join salaries s
on d.emp_no = s.emp_no
where (dept_no,s.salary) in (SELECT dept_no,max(salary) from a -- 两个字段匹配，保证一条
                             group by dept_no)
order by dept_no

'''方法二：窗口函数（你可以永远相信窗口函数0.0）'''
SELECT  dept_no
       ,emp_no
       ,salary maxSalary
FROM (
      SELECT  d.dept_no
             ,d.emp_no
             ,salary
             ,rank() over (partition by d.dept_no order by salary desc) pos
      FROM dept_emp d
      join salaries s
      on d.emp_no = s.emp_no
      ) a
where pos = 1