题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
http://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
思路:
1.查询各部门经理薪水
2.查询所有员工的信息
3.组合一下,(2)作为单表使用
4.条件 只比较同部门 员工大于经理 的薪水
SELECT
de.emp_no,
sa.emp_no as manager_no,
s1.salary,
sa.salary as manger_salary
FROM (
salaries as s1 JOIN dept_emp as de
on s1.emp_no=de.emp_no
)
JOIN
( SELECT dm.emp_no,salary,dept_no
FROM salaries as s2 JOIN dept_manager as dm
on s2.emp_no = dm.emp_no
) as sa
WHERE s1.salary > sa.salary
and de.dept_no = sa.dept_no