注意要点:选薪资最大的,所以(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;