牛客SQL题解-获取员工其当前的薪水比其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`));

输入描述:

无

输出描述:

emp_no	manager_no	emp_salary	manager_salary
10001	10002	88958	72527
10009	10010	95409	94409

 

 

 

 

 

 

答案详解

select s1.emp_no,s2.emp_no,s1.salary,s2.salary
from(select p.emp_no,p.dept_no,s.salary
from dept_emp p inner join salaries s
on p.emp_no = s.emp_no and p.to_date='9999-01-01' and s.to_date='9999-01-01'
) as s1 inner join
(
select r.emp_no,r.dept_no,s.salary
from dept_manager r inner join salaries s
on r.emp_no = s.emp_no and r.to_date='9999-01-01' and s.to_date='9999-01-01'
) as s2
on s1.dept_no = s2.dept_no where s1.salary >s2.salary