获取员工其当前的薪水比其manager当前薪水还高的相关信息

第一步

#找员工当前薪水
select de.emp_no,salary,dept_no 
     from dept_emp as de 
     left join salaries as s
     on de.emp_no=s.emp_no and s.to_date='9999-01-01'；

第二步

#找领导当前薪水
select dm.emp_no,salary,dept_no 
     from dept_manager as dm 
     left join salaries as s
     on dm.emp_no=s.emp_no and s.to_date='9999-01-01'；

最后

#找同部门中，员工薪水大于领导薪水的
select 
     员工薪水表.emp_no,
     领导薪水表.emp_no as manager_no,
     员工薪水表.salary as emp_salary,
     领导薪水表.salary as manager_salary
from
    (select de.emp_no,salary,dept_no 
     from dept_emp as de 
     left join salaries as s
     on de.emp_no=s.emp_no and s.to_date='9999-01-01') as 员工薪水表
inner join
    (select dm.emp_no,salary,dept_no 
     from dept_manager as dm 
     left join salaries as s
     on dm.emp_no=s.emp_no and s.to_date='9999-01-01') as 领导薪水表
on 员工薪水表.dept_no=领导薪水表.dept_no
where 员工薪水表.salary>领导薪水表.salary;