SELECT
    a.dept_no     dept_no,
    b.emp_no      emp_no
    a.salary      Maxsarary,

FROM
    ( 
        SELECT 
            max( salary ) salary, d.dept_no 
        FROM salaries s 
        LEFT JOIN dept_emp d 
        ON s.emp_no = d.emp_no 
        GROUP BY d.dept_no 
    ) a
    LEFT JOIN 
    ( 
        SELECT 
            s.salary, d.dept_no, d.emp_no 
        FROM salaries s 
        LEFT JOIN dept_emp d 
        ON s.emp_no = d.emp_no 
    ) b 
    ON a.salary = b.salary AND a.dept_no = b.dept_no 
ORDER BY
    a.salary

1: 派生表 a

查询每个部门中工资最高的数据 部门id和工资信息

    ( 
        SELECT 
            max( salary ) salary, d.dept_no 
        FROM salaries s 
        LEFT JOIN dept_emp d 
        ON s.emp_no = d.emp_no 
        GROUP BY d.dept_no 
    ) a

2: 派生表 b

查询所有的信息 员工id, 部门id, 薪水

    ( 
        SELECT 
            d.emp_no, d.dept_no, s.salary
        FROM salaries s 
        LEFT JOIN dept_emp d 
        ON s.emp_no = d.emp_no 
    ) b 

3: 聚合表a和表b

根据部门id和最高薪水聚合表a,b, 根据部门id排序

SELECT
    a.dept_no     dept_no,
    b.emp_no      emp_no
    a.salary      Maxsarary,

FROM
        a 
    LEFT JOIN 
    b 
    ON a.salary = b.salary AND a.dept_no = b.dept_no 
ORDER BY
    a.salary