SQL25 - 获取员工其当前的薪水比其manager当前薪水还高的相关信息

题目描述:

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) 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.emp_no,d.manager_no,d.salary as emp_salary,e.salary as manager_salary
from 
(select a.emp_no,c.emp_no as manager_no,a.salary
from 
(select emp_no,salary from salaries where to_date='9999-01-01') a
left join 
(select emp_no,dept_no from dept_emp where to_date='9999-01-01') b 
on a.emp_no = b.emp_no
left join dept_manager c on b.dept_no = c.dept_no) d 
left join (select emp_no,salary from salaries where to_date='9999-01-01') e on d.manager_no = e.emp_no
where d.salary> e.salary;