注意要点:选薪资最大的,所以(PARTITION BY dept_no ORDER BY salary DESC)要降序。

SELECT dept_no,a.emp_no,salary AS maxSalary
FROM(SELECT dept_no,dept_emp.emp_no,salary,
     RANK() over(PARTITION BY dept_no ORDER BY salary DESC) AS rk FROM dept_emp,salaries
    WHERE dept_emp.emp_no=salaries.emp_no) a
WHERE rk=1
ORDER BY dept_no;