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

SQL12 获取每个部门中当前员工薪水最高的相关信息
看到“最高”，“第N”这种字眼的题就想到用RANK()函数

SELECT 
        t1.dept_no,
        t1.emp_no,
        salary as maxSalary
FROM
    (
    SELECT
        dept_emp.dept_no,
        dept_emp.emp_no,
        salary,
        RANK() over(partition by dept_no ORDER BY salary DESC) ranks
    FROM
        dept_emp
    left join
        salaries
    ON
        salaries.emp_no = dept_emp.emp_no
    )t1
WHERE 
    t1.ranks=1
ORDER BY
    t1.dept_no ASC