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

方法）子查询分别找出员工&经理工资，再连结两表比较工资

SELECT a2.emp_no, a1.emp_no AS manager_no, a2.emp_salary, a1.manager_salary
FROM
( SELECT s1.salary AS manager_salary, dm.emp_no, dm.dept_no  -- 查询当前manager工资
FROM salaries AS s1, dept_manager AS dm
WHERE s1.emp_no = dm.emp_no
AND s1.to_date = '9999-01-01'
AND dm.to_date = '9999-01-01') a1
JOIN 
( SELECT s.salary AS emp_salary, de.emp_no, de.dept_no  -- 查询员工工资
FROM salaries AS s, dept_emp AS de
WHERE s.emp_no = de.emp_no
AND s.to_date = '9999-01-01'
AND de.to_date = '9999-01-01') a2
ON a1.dept_no = a2.dept_no
WHERE a2.emp_salary > a1.manager_salary;

天知道我报了多少遍错...表一多就容易选错列

21/3/25 更新 更简洁的代码 一遍过

方法）多表连接，连结两次salaries表来分别表示经理和员工的工资

select s1.emp_no, s2.emp_no as manager_no, s1.salary , s2.salary
from dept_emp as de join dept_manager as dm on de.dept_no = dm.dept_no
                    join salaries as s1 on s1.emp_no = de.emp_no
                    join salaries as s2 on s2.emp_no = dm.emp_no
where s2.emp_no != s1.emp_no
and s1.salary > s2.salary
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01';