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

with tmp as (select d.emp_no,salary,dept_no from dept_emp d left join salaries s on d.emp_no=s.emp_no) select a.dept_no,b.emp_no,a.maxSalary from (select dept_no,max(salary) maxSalary from tmp group by dept_no) a left join tmp b on a.dept_no=b.dept_no and b.salary=a.maxsalary order by a.dept_no asc;

1.拉宽表 2.取条件（记得group by） 3.宽表 left join 条件 4.出结果

with b as (select d.emp_no,salary,dept_no from dept_emp d left join salaries s on d.emp_no=s.emp_no), a as(select dept_no,max(salary) maxSalary from b group by dept_no) select a.dept_no,b.emp_no,a.maxSalary from a left join b on a.dept_no=b.dept_no and b.salary=a.maxsalary order by a.dept_no asc;