25.获取员工其当前的薪水比其manager当前薪水还高的相关信息
题目内容:
代码及思路:
首先要分别整理出员工和manager的emp_no还有对应salary信息。
其次在对这两者进行比较.
SELECT s1.emp_no AS emp_no, s2.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM
(SELECT sa.emp_no, de.dept_no, sa.salary FROM salaries sa INNER JOIN dept_emp de ON sa.emp_no = de.emp_no AND sa.to_date = '9999-01-01')
AS s1,
(SELECT sa.emp_no, dm.dept_no, sa.salary FROM salaries sa INNER JOIN dept_manager dm ON sa.emp_no = dm.emp_no AND sa.to_date = '9999-01-01')
AS s2
WHERE s1.dept_no = s2.dept_no
AND s1.salary > s2.salary