题目描述

获取员工其当前的薪水比其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 de.emp_no, dm.emp_no as manager_no, s1.salary as emp_salary, s2.salary as manager_salary
from dept_emp as de 
join dept_manager as dm on de.dept_no=dm.dept_no and de.to_date='9999-01-01' and dm.to_date='9999-01-01'
join salaries as s1 on de.emp_no=s1.emp_no and s1.to_date='9999-01-01'
join salaries as s2 on dm.emp_no=s2.emp_no and s2.to_date='9999-01-01'
where s1.salary>s2.salary
或
select a.emp_no, b.emp_no as manager_no, a.salary as emp_salary, b.salary as manager_salary from
(select de.emp_no, s1.salary, de.dept_no from dept_emp as de join salaries as s1 
 on de.emp_no=s1.emp_no and s1.to_date='9999-01-01') as a,
(select dm.emp_no, s2.salary, dm.dept_no from dept_manager as dm join salaries as s2
 on dm.emp_no=s2.emp_no and s2.to_date='9999-01-01') as b 
where a.dept_no=b.dept_no and a.salary>b.salary;

备注：

1. 两种方法区别在于连接的顺序不同，后一种方法效率更高。