牛客SQL12--获取每个部门中当前员工薪水最高的相关信息

MYSQL:

SELECT X.dept_no,salaries.emp_no,X.maxSalary
FROM salaries JOIN dept_emp 
ON salaries.emp_no=dept_emp.emp_no
JOIN
(SELECT dept_emp.dept_no,MAX(salaries.salary) AS maxSalary
FROM dept_emp JOIN salaries
ON dept_emp.emp_no=salaries.emp_no
GROUP BY dept_no) X
ON salaries.salary=X.maxSalary AND dept_emp.dept_no=X.dept_no
ORDER BY dept_no;