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

-- 方法一
SELECT
    d.dept_no,
    d.emp_no,
    s.salary 
FROM
    dept_emp AS d
    JOIN salaries AS s ON d.emp_no = s.emp_no 
WHERE
    ( d.dept_no, s.salary ) IN (-- in 的用法可以使用元组形式
        -- 找出部门和对应的最高工资
        SELECT
            d.dept_no,
            max( s.salary ) 
        FROM
            dept_emp AS d
            JOIN salaries AS s ON d.emp_no = s.emp_no 
        GROUP BY
            d.dept_no 
    ) -- 应付题目要求，排序输出
ORDER BY
    d.dept_no

方法二：

1. 先将每个部门最高工资和对应的部门找出来，形成表t2；
2. 再将每个人对应的工资及对应的部门找出来形成表t1
3. 再将t1和t2连接，条件是部门相同，工资相同

SELECT DISTINCT t1.dept_no,t1.emp_no,t2.maxsalary  FROM  
    (
    SELECT
        d.emp_no,d.dept_no,s.salary
    FROM 
        dept_emp d
    JOIN 
        salaries s
    ON
        d.emp_no = s.emp_no
    ) as t1
JOIN
    (
    SELECT
        d.dept_no ,
        max(s.salary) as maxsalary
    FROM 
        dept_emp d
    JOIN 
        salaries s
    ON
        d.emp_no = s.emp_no
    GROUP BY
        d.dept_no
    ) as t2
ON
t1.dept_no = t2.dept_no
AND    
t1.salary = t2.maxsalary
ORDER BY
t1.dept_no