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

select t1.dept_no,t2.emp_no,t1.maxSalary from
(select d.dept_no, any_value(d.emp_no), max(s.salary) as maxSalary from dept_emp d inner join salaries as s 
on d.emp_no = s.emp_no
where d.to_date = '9999-01-01'
group by d.dept_no
order by d.dept_no asc) as t1,
(select d.dept_no, s.salary,d.emp_no from dept_emp d inner join salaries s
on d.emp_no = s.emp_no) as t2
where t2.salary = t1.maxSalary and t2.dept_no = t1.dept_no
order by t1.dept_no asc

确实挺难的

表1先得到每个部门最高的工资

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

on d.emp_no = s.emp_no

where d.to_date = '9999-01-01'

group by d.dept_no

order by d.dept_no asc) as t1

表2得到每个员工的工资

最后用表2来过滤表1得到 最终结果