获取员工当前的薪水比其manager薪水还高的员工的薪水和manager的薪水

题目描述

有一个，部门关系表dept_emp简况如下:

有一个部门经理表dept_manager简况如下:

有一个薪水表salaries简况如下:

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

第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

以上例子输出如下:

多表连接

SELECT de.emp_no,dm.emp_no manager_no,
sa.salary emp_salary,sal.salary manager_salary
FROM dept_emp de,salaries sa,dept_manager dm,salaries sal
WHERE de.emp_no=sa.emp_no
AND dm.emp_no=sal.emp_no
AND de.dept_no=dm.dept_no
AND sa.salary>sal.salary

查询员工工资、经理工资再对两个结果进行连接

**查询员工当前工资表 emp_sal**
select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
**查询经理当前工资表mag_sal**
 select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
**联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
)as emp_sal
inner join(
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;

赏

谢谢你请我吃糖果

微信