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

select d.dept_no,d.emp_no,s.salary maxSalary from salaries as s
inner join dept_emp d on d.emp_no = s.emp_no 
AND d.to_date='9999-01-01'
AND s.to_date='9999-01-01'
where s.salary in 
(select max(s1.salary) from salaries as s1
 inner join dept_emp d1 on d1.emp_no=s1.emp_no
 AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
 AND d.dept_no=d1.dept_no 
group by d1.dept_no)
order by d.dept_no;

//此题的注意点就是当使用group by的时候，select语句不能使用除了聚合函数以及group by后面那一列的其他列。