题解 | 困难:获取每个部门中当前员工薪水最高的相关信息(利用好子查询，聚合字段和非聚合字段)

个人最后答案：

select d.dept_no,d.emp_no,salary as maxSalary from dept_emp as d,salaries as s 

where d.emp_no = s.emp_no and d.to_date = '9999-01-01' and s.to_date='9999-01-01' 

and (d.dept_no,salary) in

(select d.dept_no,max(s.salary) as maxSalary from dept_emp as d , salaries as s 

where 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 )

order by d.dept_no

读题最早的思路:根据两张表的部门编号进行两表关联，同时用离职日期to_date做为当前员工的一个限制条件，更加部门编号分组再根据部门编号排序(大部分人的第一想法)

版本1.0:

select d.dept_no,s.emp_no,max(salary) as maxSalary from dept_emp as d,salaries as s 

where s.emp_no = d.emp_no and d.to_date='9999-01-01' 

group by d.dept_no 

order by d.dept_no;

出现了一个部门多个个员工，执行出来部门编号正确，员工编号错误（sql选择的不是我们要的那个，员工编号没做好限制导致），最大薪资正确

版本2.0:

select d.dept_no,s.emp_no,s.salary as maxSalary from dept_emp as d,salaries as s 

where s.emp_no = d.emp_no and s.to_date = '9999-01-01' and d.to_date='9999-01-01' 

group by d.dept_no having s.salary = max(salary)

order by d.dept_no;

做到这里,就应该想到应该分步子查询了，首先在子查询中分组的同时，把每组最大薪资查出来，然后外部根据部门和最大薪资进行选择匹配加上员工编号条件，就会伴随地选出对应的员工编号了