获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

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

思路：
dept_emp左联salaries，emp_no相同，得出emp_no,salary
dept_emp左联dept_manager，dept_no相同，输出dept_manager的emp_no
我不知道咋打印manager_salary

select de.emp_no,dm.emp_no as manager_no,s.salary,r.salary AS manager_salary,s.to_date,r.to_date 
from dept_emp AS de 
left JOIN salaries AS s 
    ON de.emp_no=s.emp_no AND s.to_date='9999-01-01'
left join dept_manager AS dm
        ON de.dept_no=dm.dept_no
left join salaries AS r
        ON dm.emp_no=r.emp_no AND r.to_date='9999-01-01'
    /*一开始写成r了 就打印不出来*/
where de.emp_no not in (select emp_no from dept_manager)

答案：

SELECT de.emp_no,dm.emp_no manager_no,
sa.salary emp_salary,sal.salary manager_salary
FROM dept_emp de,salaries sa,dept_manager dm,salaries sal
WHERE de.emp_no=sa.emp_no
AND dm.emp_no=sal.emp_no
AND de.dept_no=dm.dept_no
AND de.to_date='9999-01-01'
AND sa.to_date='9999-01-01'
AND dm.to_date='9999-01-01'
AND sal.to_date='9999-01-01'
AND sa.salary>sal.salary