获取员工其当前的薪水比其manager当前薪水还高的相关信息,给出员工的emp_no,其manager的manager_no,该员工当前的薪水emp_salary,该员工对应的manager当前的薪水manager_salary
将员工部门表dept_emp与薪资表salaries连接起来查询(所有员工,也包括manager)
select d.dept_no as dept_no, d.emp_no as emp_no, salary from dept_emp d join salaries s on (d.emp_no = s.emp_no)
将manager部门表dept_manager与薪资表salaries连接起来查询
select dm.dept_no, dm.emp_no as manager_no, salary from dept_manager dm join salaries s on (dm.emp_no = s.emp_no)
再将这两个查询到的数据当作两个新表t1、t2,然后通过dept_no来进行连接
使用条件 t1.salary > t2.salary 来实现查询员工其当前的薪水比其manager当前薪水还高的相关信息
最后使用 group by t1.dept_no 来限制为是同部门之间的薪水来比较(数据量不多,我没写这句好像也能行)
最终答案:
select t1.emp_no emp_no, t2.manager_no as manager_no, t1.salary as emp_salary, t2.salary as manager_salary from (select d.dept_no as dept_no, d.emp_no as emp_no, salary from dept_emp d join salaries s on (d.emp_no = s.emp_no)) t1 join (select dm.dept_no, dm.emp_no as manager_no, salary from dept_manager dm join salaries s on (dm.emp_no = s.emp_no)) t2 on (t1.dept_no = t2.dept_no) where t1.salary > t2.salary group by t1.dept_no
显示结果: