【Mysql】题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#

题目描述：获取员工其当前的薪水比其manager当前薪水还高的相关信息，第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary。

分块写法（逻辑更清晰）：

with manager as #当前经理当前薪水情况
(
    select dm.dept_no,
           dm.emp_no as manager_no,
           s.salary as manager_salary
    from dept_manager dm join salaries s
                         on dm.emp_no=s.emp_no
    where s.to_date="9999-01-01"
      and dm.to_date="9999-01-01"
    group by dm.dept_no
),
salary as #当前普工当前薪水与对应经理薪水情况
(
    select de.emp_no, 
           m.manager_no,
           s.salary as emp_salary,
           m.manager_salary
    from dept_emp de join manager m on de.dept_no=m.dept_no
                     join salaries s on de.emp_no=s.emp_no
    where s.to_date="9999-01-01"
     and de.to_date="9999-01-01"   
)
#找出员工薪水大于经理的记录
select * 
from salary
where emp_salary > manager_salary

同样的思路放在一起的写法(代码相对较量少):

select de.emp_no as emp_no, 
       dm.emp_no as manager_no, 
       s1.salary as emp_salary,
       s2.salary as manager_salary
from dept_emp de, 
     dept_manager dm, 
     salaries s1, 
     salaries s2
where de.dept_no=dm.dept_no
  and de.emp_no=s1.emp_no
  and dm.emp_no=s2.emp_no
  and de.to_date="9999-01-01"
  and dm.to_date="9999-01-01"
  and s1.to_date="9999-01-01"
  and s2.to_date="9999-01-01"
  and s1.salary>s2.salary