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

可以把主要信息进行提取，变成manager的工资表和员工工资表，再将这两个表连接，提取题目要求的信息

SELECT sem.emp_no AS emp_no,
    sdm.emp_no AS manager_no,
    sem.salary AS emp_salary, 
    sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no 
      FROM salaries s INNER JOIN dept_emp de
      ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) 
      AS sem, 
    (SELECT s.salary, s.emp_no, dm.dept_no 
     FROM salaries s INNER JOIN dept_manager dm
     ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' )
     AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary