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

题解自用（参考一下用户解答） #reference: 👉👀201908161815342

• 关于group by的一些用法限制：

  • Only items allowed in the select list of query with group by clause are,
    • Expressions in Group BY
    • Aggregate Functions (SUM, COUNT, MAX, Min, AVG)
  • Expressions in the group by clause need not to be included in select statement

• 为什么合并3个表：

  • 因为new这个表里面没有emp_no, 如果想要dept_no, emp_no, salary必须通过三个表一起实现
  • new.dept_no -->connect dept_emp.dept_no; dept_emp.emp_no -->connect salaries.emp_no

select new.dept_no, ss.emp_no, ss.salary from (select d.dept_no, max(s.salary) as maxSalary from dept_emp d inner join salaries s 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 new, salaries ss, dept_emp dd 
where new.maxSalary = ss.salary
and new.dept_no = dd.dept_no
and dd.emp_no = ss.emp_no
and ss.to_Date = '9999-01-01' and dd.to_date = '9999-01-01'
order by new.dept_no asc