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

用子查询分别获取manager_no, manager_salary。

select de.emp_no, 
(select dm1.emp_no from dept_manager as dm1 
 where de.dept_no = dm1.dept_no) as manager_no,
s.salary,
(select s2.salary 
 from salaries as s2, dept_manager as dm2
 where de.dept_no = dm2.dept_no and
 	   dm2.emp_no = s2.emp_no) as manager_salary
from dept_emp as de, salaries as s
where de.emp_no = s.emp_no and 
      s.salary > (select s2.salary 
                  from salaries as s2, dept_manager as dm2
                  where de.dept_no = dm2.dept_no and
                        dm2.emp_no = s2.emp_no)

第一个salaries表和employee表连接，第二个salaries表和manager表连接，然后限定员工和manager同一部门并且员工工资大于manager工资。

select de.emp_no, dm.emp_no, s1.salary, s2.salary
from dept_emp as de, dept_manager as dm, salaries as s1, salaries as s2
where de.emp_no=s1.emp_no and dm.emp_no=s2.emp_no and 
      de.dept_no=dm.dept_no and s1.salary > s2.salary and
      de.to_date="9999-01-01" and dm.to_date="9999-01-01"