题解 | #查找在职员工自入职以来的薪水涨幅情况#

sql script

# entry salary
select e.emp_no, s.salary
from employeess e, salaries s 
where e.emp_no=s.emp_no and e.hire_date=s.from_date


# dimission salary
select e.emp_no, s.salary
from employeess e, salaries s 
where e.emp_no=s.emp_no and s.to_date='9999-01-01'

select entry.emp_no, dimission.salary-entry.salary as growth
from (select e.emp_no, s.salary
      from employees e, salaries s
      where e.emp_no=s.emp_no and e.hire_date=s.from_date) as entry,
      (select e.emp_no, s.salary
       from employees e, salaries s
       where e.emp_no=s.emp_no and s.to_date='9999-01-01') as dimission
where entry.emp_no=dimission.emp_no
order by growth

solution

Pay attention to the question keyword like the 在职 and 入职以来
Firstly, calculate the 在职 employees and 入职以来 salary
Secondly, calculate the 离职 salary
Thirdly, do inner join to use dimission salary to minus entry salary in 在职 employees