题解 | 获取员工其当前的薪水比其manager当前薪水还高的相关信息
获取员工其当前的薪水比其manager当前薪水还高的相关信息
https://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
select
t1.emp_no as emp_no,
t2.emp_no as manager_no,
t1.salary as emp_salary,
t2.salary as manager_salary
from
( -- 普通员工 部门 薪水
select
de.emp_no,
de.dept_no,
s.salary
from
dept_emp de
join salaries s on de.emp_no = s.emp_no
) as t1
join ( -- 作为manger的员工 部门 薪水
select
dm.emp_no,
dm.dept_no,
s.salary
from
dept_manager dm
join salaries s on dm.emp_no = s.emp_no
) as t2 on t1.dept_no = t2.dept_no
and t1.salary > t2.salary
