题解 | #题解#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
https://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
-- 创建临时表 tem_1，选择除部门经理外其他员工的员工号、部门号和薪水
with tem_1 as (
select a.emp_no, a.dept_no, sa.salary
from (
select de.emp_no, de.dept_no
from dept_emp de, dept_manager dm
where de.dept_no = dm.dept_no and de.emp_no != dm.emp_no
) a, salaries sa
where a.emp_no = sa.emp_no
),
-- 创建临时表 tem_2，选择部门经理的员工号、部门号和薪水
tem_2 as (
select b.emp_no, b.dept_no, sa.salary
from (
select de.emp_no, de.dept_no
from dept_emp de, dept_manager dm
where de.dept_no = dm.dept_no and de.emp_no = dm.emp_no
) b, salaries sa
where b.emp_no = sa.emp_no
)
-- 选取 tem_1 和 tem_2 中符合薪水高于部门经理的员工号、部门经理号以及员工和部门经理的薪水
select t1.emp_no, t2.emp_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary
from tem_1 t1, tem_2 t2
where t1.dept_no = t2.dept_no and t1.salary > t2.salary
投递蚂蚁集团等公司10个岗位
