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

# 取每个部门中当前员工薪水最高的相关信息
# 方法一（反面案例）：别名+内联接+子查询+MAX函数  理解错误，所有部门当前员工最高工资
# SELECT de.dept_no, de.emp_no, s.salary
# FROM dept_emp AS de
# INNER JOIN salaries AS s
# ON de.emp_no = s.emp_no
# WHERE salary = (
# SELECT MAX(salary) FROM salaries
# )
# AND de.to_date = "9999-01-01"
# AND s.to_date = "9999-01-01"
# GROUP BY dept_no
# ORDER BY dept_no
# 方法二：内联接+MAX函数    受MAX函数限制，只能找到单个最大的salary，不能找到多个最大的salary
# SELECT de.dept_no, de.emp_no, MAX(s.salary) AS max_salary
# FROM dept_emp AS de
# INNER JOIN salaries AS s
# ON de.emp_no = s.emp_no
# WHERE de.to_date = "9999-01-01"
# AND s.to_date = "9999-01-01"
# GROUP BY de.dept_no
# ORDER BY de.dept_no
# 鉴于方法二的局限性，即创建另一张表（员工表+新水表）
# 方法三：创建两张表（最高薪水表、所有部门员工薪水表），
# 进行dept_no和salary键值进行比较，得出每部门相同最高工资的相关信息
SELECT uni.dept_no, uni.emp_no, max_salary.salary
FROM
(SELECT de1.dept_no, s1.emp_no, s1.salary
FROM dept_emp AS de1
INNER JOIN salaries AS s1
ON de1.emp_no = s1.emp_no
WHERE de1.to_date = "9999-01-01"
AND s1.to_date = "9999-01-01"
ORDER BY s1.emp_no
     ) AS uni      #每个部门所有员工的工资表
INNER JOIN
(SELECT de2.dept_no, MAX(s2.salary) AS salary
FROM dept_emp AS de2
INNER JOIN salaries AS s2
ON de2.emp_no = s2.emp_no
WHERE de2.to_date = "9999-01-01"
AND s2.to_date = "9999-01-01"
GROUP BY de2.dept_no
           ) AS max_salary    #部门最高工资表
ON uni.dept_no = max_salary.dept_no
AND uni.salary = max_salary.salary
ORDER by uni.dept_no