记一个sql题：select中既有聚合字段，又有非聚合字段，出错

一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，select 选择的时候，会随机选择非聚合字段中的任何一个，会出错

题目描述

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

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

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

第一次尝试：

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

报错 ：
答案应该是 **d004|10004|74057 ** 但是输出了 d004|10003|74057
原因：
dept_emp.emp_no是非聚合字段，不能出现在SELECT。

一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，选择的时候，会随机选择非聚合字段中的任何一个，于是出错。

改正：

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

# 关联子查询，外表固定一个部门，内表进行子查询
SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
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(s.salary)
FROM dept_emp as d
JOIN salaries as s
ON d.emp_no=s.emp_no AND d.to_date='9999-01-01' AND s.to_date='9999-01-01'AND d.dept_no = d1.dept_no
)
ORDER BY d1.dept_no;

（2）用两个临时表结合

select t1.dept_no, t1.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 t1, /* 部门编号,员工编号,当前薪水 */
    (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 t1.salary = max_salary.salary and t1.dept_no = max_salary.dept_no
order by t1.dept_no;