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

-- 每个部门的最高工资

-- select d1.dept_no,max(s1.salary) salary from dept_emp d1 join salaries s1 on d1.emp_no = s1.emp_no group by d1.dept_no

-- 查询员工工资信息 -- select d.emp_no,d.dept_no,s.salary from dept_emp d join salaries s on d.emp_no = s.emp_no;

-- 二表链接

select t2.dept_no,t2.emp_no,t1.salary from (select d1.dept_no,max(s1.salary) salary from dept_emp d1 join salaries s1 on d1.emp_no = s1.emp_no group by d1.dept_no) t1 join (select d.emp_no,d.dept_no,s.salary from dept_emp d join salaries s on d.emp_no = s.emp_no) t2 on t1.dept_no = t2.dept_no and t1.salary = t2.salary order by dept_no;