方法)子查询分别找出员工&经理工资,再连结两表比较工资
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';