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

select dept_no,emp_no,salary as maxSalary from (
        select
            d.dept_no as dept_no,
            d.emp_no as emp_no,
            s.salary as salary,
            row_number() over (partition by dept_no order by salary DESC) as row_num
        from
            dept_emp as d
            left join salaries as s on d.emp_no = s.emp_no
    )as sub_query
where row_num = 1

1、先查出所有员工的部门、对应工资，将工资根据部门来排序 从大到小。这个结果中，行号为1的，就是最大的工资

2、在结果中只要行号为1的

注意，子查询一定要有别名alias