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

select
  cf.dept_no,
  salaries.emp_no,
  cf.maxx
from
  dept_emp,
  salaries,
  (
    select
      mm.dept_no,
      max(mm.maxSalary) as maxx
    from
      dept_emp,
      salaries,
      (
        select
          dept_emp.dept_no,
          max(salaries.salary) as maxSalary
        from
          dept_emp,
          salaries
        where
          dept_emp.emp_no = salaries.emp_no
        group by
          dept_emp.dept_no
      ) as mm
    where
      dept_emp.emp_no = salaries.emp_no
      and mm.dept_no = dept_emp.dept_no
    group by
      mm.dept_no
  ) as cf
where
  cf.dept_no = dept_emp.dept_no
  and salaries.emp_no = dept_emp.emp_no
  and salaries.salary = cf.maxx
  and dept_emp.dept_no = cf.dept_no
order by
  cf.dept_no asc;