【牛客sql实战12】获取所有部门中当前员工薪水最高的相关信息（亲测通过）

题目描述

获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列。

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

实现

子查询

select d.dept_no, d.emp_no, s.salary
from dept_emp as d
inner join salaries as s on d.emp_no=s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
where s.salary in 
(select max(s1.salary)
    from dept_emp as d1
    inner join salaries as s1 on d1.emp_no=s1.emp_no
    and d1.to_date='9999-01-01'
    and s1.to_date='9999-01-01'
    and d1.dept_no = d.dept_no
)
order by d.dept_no;