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

sql script

# employees salary with departments
# select de.emp_no as emp_no, de.dept_no, s.salary as salary
# from dept_emp de,salaries s
# where de.emp_no=s.emp_no 
# and de.emp_no not in (select emp_no from dept_manager)

# manager salary
# select dm.emp_no as emp_no, dm.dept_no as dept_no, s.salary as salary
# from dept_manager dm, salaries s
# where dm.emp_no=s.emp_no

select s1.emp_no as emp_no, s2.emp_no as manager_no, s1.salary as emp_salary, s2.salary as manager_salary
from (select de.emp_no as emp_no, de.dept_no,s.salary as salary
      from dept_emp de,salaries s
      where de.emp_no=s.emp_no 
      and de.emp_no not in (select emp_no from dept_manager)) s1,
      (select dm.emp_no as emp_no, dm.dept_no as dept_no, s.salary as salary
       from dept_manager dm, salaries s
       where dm.emp_no=s.emp_no) s2
where s1.dept_no=s2.dept_no
and s1.salary > s2.salary

key point

separate the employees and manager emp_no, dept_no, salary
then do the join function and compare with two tables