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

select b.dept_no,b.emp_no,b.salary maxSalary  from (
    select @row_number := 
    CASE WHEN @dept_no = a.dept_no THEN @row_number + 1 ELSE 1 END AS num,
    @dept_no := a.dept_no as dept_no,
    a.salary,
    a.emp_no
from 
(select de.dept_no,s.emp_no,s.salary from dept_emp de
inner join salaries s on s.emp_no = de.emp_no) a,
(select @row_number := 0,@dept_no := 0) as t
order by a.dept_no,a.salary desc
) b where b.num = 1

首先对dept_emp表使用dept_no分组，然后按照salary排序，得到每个部门（dept_no）下的各个员工的salary倒序排序值num,最后再取num = 1（即最高薪）的员工。