牛客SQL练习-12-获取所有部门中当前员工当前薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。

**注意：**题干未说明每个部门最大薪水是否唯一，可能存在多个最大值情况

SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
FROM 
-- 创建maxsalary表用于存放当前每个部门薪水的最大值
(SELECT d.dept_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_no) AS maxsalary, 
-- 创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
(SELECT d.dept_no, s.emp_no, s.salary 
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
) AS currentsalary
-- 限定条件为两表的dept_no和salary均相等
WHERE currentsalary.dept_no = maxsalary.dept_no
AND currentsalary.salary = maxsalary.salary
-- 最后以currentsalary.dept_no排序输出符合要求的记录表
ORDER BY currentsalary.dept_no