题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
http://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
select distinct t1.emp_no,t3.emp_no manger_no,t1.salary,t3.salary
from
(select emp_no,salary from salaries) t1
join
(select de.dept_no,de.emp_no
from dept_emp de join dept_manager dm on de.emp_no != dm.emp_no) t2
on t1.emp_no = t2.emp_no
join
(select dm.emp_no,dm.dept_no,s.salary from dept_manager dm
join salaries s on dm.emp_no = s.emp_no) t3
on t3.dept_no = t2.dept_no
where t1.salary > t3.salary
from
(select emp_no,salary from salaries) t1
join
(select de.dept_no,de.emp_no
from dept_emp de join dept_manager dm on de.emp_no != dm.emp_no) t2
on t1.emp_no = t2.emp_no
join
(select dm.emp_no,dm.dept_no,s.salary from dept_manager dm
join salaries s on dm.emp_no = s.emp_no) t3
on t3.dept_no = t2.dept_no
where t1.salary > t3.salary