SQL实战25.获取员工其当前的薪水比其manager当前薪水还高的相关信息

题目

解答

思路：创建两张表，一张员工表，一张经理表，进行联结

--员工表 
(select de.emp_no,de.dept_no,s.salary as emp_salary	
from dept_emp as de left join salaries as s
on de.emp_no=s.emp_no
where de.emp_no not in (select emp_no from dept_manager)
and s.to_date='9999-01-01') as a

--经理表
(select dm.emp_no as manager_no,dm.dept_no,s.salary as manager_salary
from dept_manager as dm left join salaries as s
on dm.emp_no=s.emp_no
where s.to_date='9999-01-01') as b

联结，并给出条件

select a.emp_no,b.manager_no,a.emp_salary,b.manager_salary
from (select de.emp_no,de.dept_no,s.salary as emp_salary	
from dept_emp as de left join salaries as s
on de.emp_no=s.emp_no
where de.emp_no not in (select emp_no from dept_manager)
and s.to_date='9999-01-01') as a left join 
(select dm.emp_no as manager_no,dm.dept_no,s.salary as manager_salary
from dept_manager as dm left join salaries as s
on dm.emp_no=s.emp_no
where s.to_date='9999-01-01') as b
on a.dept_no=b.dept_no
where a.emp_salary>b.manager_salary

二刷

select a.emp_no,a.manager_no,a.emp_salary,b.salary as manager_salary
from
(select de.emp_no,dm.emp_no as manager_no,s.salary as emp_salary
from dept_emp as de
join dept_manager as dm
on de.dept_no = dm.dept_no 
join salaries as s
on de.emp_no = s.emp_no
where de.emp_no != dm.emp_no) a
left join salaries as b
on a.manager_no = b.emp_no
where a.emp_salary > b.salary;

select t1.emp_no,t2.emp_no,t1.salary,t2.salary
from salaries as t1
join dept_emp as de
on t1.emp_no = de.emp_no
join (  select dm.dept_no,dm.emp_no,s.salary
        from dept_manager as dm
        join salaries as s
        on dm.emp_no = s.emp_no) t2 
on de.dept_no = t2.dept_no
where t1.salary>t2.salary;