题解 | #查找最晚入职员工的所有信息#

select c.dept_no,c.emp_no,c.salary 
from 
(select dept_no,d.emp_no as emp_no,salary from dept_emp as d,salaries as s where d.emp_no=s.emp_no) as c 
join
(
    select dept_no,max(salary) as salary from 
    (select dept_no ,d.emp_no as emp_no,salary from dept_emp as d join salaries as s on d.emp_no=s.emp_no order by d.dept_no,salary desc) as a 
    group by dept_no) as b 
on c.dept_no=b.dept_no and c.salary=b.salary 
order by c.dept_no;

思路： 1.先组合两表作为新表a（dept_no，emp_no，salary） 2.根据dept_no和salary排序取出dept_no和max(salary) 作为新表b 3.关联cb两表on dept_no 和 salary 4.根据dept_no排序