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

1.先查询每个员工的编号、部门和薪水

	 SELECT A.dept_no,A.emp_no,B.salary
     FROM dept_emp A
     JOIN salaries B
     ON A.emp_no = B.emp_no

2.在查询出每个部门最高的薪水

	SELECT A.dept_no,MAX(salary) salary
    FROM dept_emp A
    JOIN salaries B
    ON A.emp_no = B.emp_no
    GROUP BY A.dept_no

3.将上述两个查询作为两张表联合查询,连接条件是dept_no,过滤条件是salary需相等,然后再排序即可：

T1.dept_no,T1.emp_no,T1.salary
FROM 
        (SELECT A.dept_no,A.emp_no,B.salary
         FROM dept_emp A
         JOIN salaries B
         ON A.emp_no = B.emp_no) T1
JOIN 
        (SELECT A.dept_no,MAX(salary) salary
        FROM dept_emp A
        JOIN salaries B
        ON A.emp_no = B.emp_no
        GROUP BY A.dept_no) T2
ON T1.dept_no = T2.dept_no
WHERE T1.salary = T2.salary
ORDER BY T1.dept_no