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

#每个部门最高的薪资怎么求,以下表设为t1
/*
select 
    a.dept_no,max(b.salary) salary
from 
    salaries b join dept_emp a 
on 
    b.emp_no = a.emp_no
group by 
    a.dept_no;
    
#部门，员工，薪水对应表 t2
select 
    a.dept_no,a.emp_no,b.salary
from 
    salaries b join dept_emp a 
on 
    b.emp_no = a.emp_no;*/

select t1.dept_no,t2.emp_no,t1.salary
    from 
    (
        select a.dept_no,max(b.salary) salary
            from 
                salaries b join dept_emp a 
            on 
                b.emp_no = a.emp_no
            group by 
                a.dept_no
        ) 
        t1 join 
        (   
            select 
                d.dept_no,d.emp_no,s.salary
            from 
                salaries s join dept_emp d 
            on 
                s.emp_no = d.emp_no
            ) t2 
            where 
                t1.dept_no =t2.dept_no and t1.salary=t2.salary

                order by t1.dept_no;
            ;