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

来源：牛客网
链接：https://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef?tpId=82&&tqId=29777&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

Sql语句

思路：员工和经理表分别和新水表连接，再连接

select a.emp_no, b.emp_no, a.salary, b.salary
from (select dept_emp.emp_no, salaries.salary, dept_emp.dept_no
      from dept_emp, salaries
      where salaries.to_date='9999-01-01'
      and dept_emp.emp_no = salaries.emp_no) as a,
     (select dept_manager.emp_no, salaries.salary, dept_manager.dept_no
      from dept_manager, salaries
      where salaries.to_date='9999-01-01'
      and dept_manager.emp_no = salaries.emp_no) as b
where a.dept_no = b.dept_no
and a.salary > b.salary