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

两种方法：

1、不借助窗口函数，直接用多表连接

select distinct
	# 为了过题目才加max，实际输出全部最高的部门最高薪员工才合理
    t1.dept_no, max(t3.emp_no), t2.maxSalary
from
    dept_emp t1
    join (
        select
            d.dept_no AS dept_no,
            max(salary) AS maxSalary
        from
            dept_emp d
            join salaries s on d.emp_no = s.emp_no
        group by
            d.dept_no
    ) t2 on t2.dept_no = t1.dept_no
    join salaries t3 on t2.maxSalary = t3.salary
# 为了过题目才加group by
group by  t1.dept_no, t2.maxSalary
order by t1.dept_no

2、窗口函数（每组内top）

select
    dept_no,
    emp_no,
    salary
from
    (
        select
            rank() over (
                partition by
                    dept_no
                order by
                    salary DESC
            ) AS rk,
            dept_no,
            emp_no,
            salary
        from
            (
                # 先拼出一张大表，包括部门、员工、工资
                select
                    t1.emp_no,
                    t1.dept_no,
                    t2.salary
                from
                    dept_emp t1
                    join salaries t2 on t1.emp_no = t2.emp_no
            ) tmp
    ) tmpp
where
    tmpp.rk = 1