牛客--SQL12.获取每个部门中当前员工薪水最高的相关信息

题目：

有一个员工表dept_emp简况如下:

有一个薪水表salaries简况如下：

 

 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

（注意: Mysql与Sqlite select 非聚合列的结果可能不一样)

代码：

1、错误代码：

select dept_no,salaries.emp_no,max(salary) as maxSalary from dept_emp,salaries where dept_emp.emp_no=salaries.emp_no
and dept_emp.to_date='9999-01-01' and salaries.to_date='9999-01-01' group by dept_no order by dept_no

错误原因：

• salaries.emp_no是非聚合字段，不能出现在select。因为一个聚合字段（dept_no）对应多个非聚合字段（emp_no），所以选择的时候会随机选择非聚合字段中的一个
• 当多人同时拥有最高薪水时，也无法查出

因为聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为GROUP BY语句中指定的字段，要么是常数，否则会出错。
简而言之：使用GROUP BY子句时，SELECT子句中只能有聚合键、聚合函数、常数。

2、正确代码：

关联子查询，外表固定一个部门，内表进行子查询

select d1.dept_no,d1.emp_no,s1.salary from dept_emp as d1 inner join salaries as s1 on d1.emp_no=s1.emp_no
and d1.to_date='9999-01-01' and s1.to_date='9999-01-01'
where s1.salary in (select max(s2.salary) from dept_emp as d2 inner join salaries as s2 on d2.emp_no=s2.emp_no
and d2.to_date='9999-01-01' and s2.to_date='9999-01-01' and d1.dept_no=d2.dept_no)  
order by d1.dept_no

也可以有另一种写法：

select uni.dept_no, uni.emp_no, max_salary.salary
from
    (select d.dept_no, s.emp_no, s.salary
     from dept_emp d join salaries s
     on d.emp_no = s.emp_no
     and d.to_date = '9999-01-01'
     and s.to_date = '9999-01-01'  
    ) as uni, /* 部门编号,员工编号,当前薪水 */
    (select d.dept_no, max(s.salary) as salary
     from dept_emp d join salaries s
     on d.emp_no = s.emp_no
     and d.to_date = '9999-01-01'
     and s.to_date = '9999-01-01'  
     group by d.dept_no
    ) as max_salary /* 部门编号,当前最高薪水 */
where uni.salary = max_salary.salary
and uni.dept_no = max_salary.dept_no
order by uni.dept_no