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

with allzaizhi as (
    select emp_no from salaries
    where to_date = '9999-01-01'
),
finalsalary as(
    select s.emp_no, s.salary from salaries s 
    where (s.emp_no in (SELECT emp_no FROM allzaizhi))
    and s.to_date = '9999-01-01'
),
beginday as(
    select s.emp_no, min(from_date) as fromdate from salaries s 
    where (s.emp_no in (SELECT emp_no FROM allzaizhi))
    group by s.emp_no
),
beginsalary as (
    select s.emp_no, salary as salary from salaries s 
    where (s.emp_no in (SELECT emp_no FROM allzaizhi)) and 
    (s.from_date in (select fromdate from beginday))
)
select b.emp_no, f.salary - b.salary as growth from beginsalary b inner join finalsalary f 
on b.emp_no = f.emp_no
order by growth asc

我用了四个表，

第一个表：查出在职的人员有哪些

第二个表：查出在职的人员目前的工资、工号

第三个表：查出在职人员入职的日期

第四个表：查出在职人员入职的日期对应的工资

然后就很好办了，目前工资-入职工资即可

注意查在职人员的入职工资用了两个表，因为可能有多条工资调整记录，所以要先找到入职的日期