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

# # 步骤一：员工和薪水
# select dept_no, emp_no, salary as emp_salary
# from dept_emp
# left join salaries using(emp_no)
# where to_date = '9999-01-01'

# # 步骤二：manager和薪水
# select dept_no, emp_no as manager_no, salary as manager_salary
# from dept_manager as dm
# left join salaries as sa using(emp_no)
# where sa.to_date = '9999-01-01'

# 步骤三：获取员工其当前的薪水比其manager当前薪水还高的相关信息
select t1.emp_no, t2.manager_no, t1.emp_salary, t2.manager_salary
from (
    # 步骤一：员工和薪水
    select dept_no, dept_emp.emp_no, salary as emp_salary
    from dept_emp
    left join salaries using(emp_no)
    where salaries.to_date = '9999-01-01') as t1
left join (
    # 步骤二：manager和薪水
    select dept_no, emp_no as manager_no, salary as manager_salary
    from dept_manager as dm
    left join salaries as sa using(emp_no)
    where sa.to_date = '9999-01-01') as t2 using(dept_no)
where emp_salary > manager_salary;