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

/*
#找到员工表
select t.e, t.s, t.d
from (select de.emp_no as e, s.salary as s, de.dept_no as d
     from dept_emp as de
     join salaries as s
     on s.emp_no = de.emp_no) as t,
dept_manager as manager
where manager.emp_no <> t.e
#连接经理表和工资表
select man.emp_no, s.salary
from dept_manager as man
join salaries as s
on man.emp_no = s.emp_no
*/
SELECT distinct t0.e as emp_no, t1.e as manager_no, t0.s as emp_salary, t1.s as manager_salary
FROM (select t.e as e, t.s as s, t.d as d
from (select de.emp_no as e, s.salary as s, de.dept_no as d
     from dept_emp as de
     join salaries as s
     on s.emp_no = de.emp_no) as t,
dept_manager as manager
where manager.emp_no <> t.e) as t0,
(select man.emp_no as e, s.salary as s, man.dept_no as d
from dept_manager as man
join salaries as s
on man.emp_no = s.emp_no) as t1
where t0.d = t1.d 
and t0.s > t1.s;