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

答案

select emp.emp_no, manager.emp_no manager_no, 
emp.salary emp_salary, manager.salary manager_salary
from (
    select de.emp_no, de.dept_no, s.salary
    from salaries s, dept_emp de
    where s.emp_no = de.emp_no
    ) emp, /*查询员工emp_no, dept_no, salary*/
    (
    select dm.emp_no, dm.dept_no, s.salary
    from salaries s, dept_manager dm
    where s.emp_no = dm.emp_no
    ) manager /*查询manager的emp_no, dept_no, salary*/
where emp.dept_no = manager.dept_no
and emp.salary > manager.salary;

思路

1. 内连接salary和dept_emp，查询员工emp_no, dept_no, salary
2. 内连接salary和dept_manager，查询manager的emp_no, dept_no, salary
3. 内连接1和2，查询员工其当前的薪水比其manager当前薪水还高的信息

优化

复制高排行答案，来自@xiuxd

select de.emp_no, dm.emp_no as manager_no,
    s1.salary as emp_salary, s2.salary as manager_salary
from dept_emp de, dept_manager dm, salaries s1, salaries s2
where de.dept_no = dm.dept_no
and de.emp_no = s1.emp_no
and dm.emp_no = s2.emp_no
and s1.salary > s2.salary
and s2.to_date = '9999-01-01'
and s1.to_date = '9999-01-01';