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

select
    n.emp_no,
    n.salary - o.salary as growth
from(
    select 
    s.emp_no, s.salary 
    from salaries s 
    join employees e on e.hire_date = s.from_date
) o
# 入职工资 Onboarding
join(
    select
    emp_no,
    to_date,
    salary
    from salaries
    where to_date = '9999-01-01'
) n
# 当前工资 now
    on o.emp_no = n.emp_no
order by
    growth

这道题是查找在职员工自入职以来的薪水涨幅情况(注意这里强调的是在职员工)，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为

（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）

【注意】这道题的前提是职员工资不会下降，在职职工的当前工资就是最高工资，在职职工的入职工资是最低工资

四个关键点：

1.查找在职员工的入职工资。按照员工的入职日期hire_date和员工的开始发工资的日期from_date相等连接salaries表和employees表，得到每个职工的入职工资。

    select 
    s.emp_no, s.salary 
    from salaries s 
    join employees e on e.hire_date = s.from_date

2.查找在职员工的当前工资。在salaries表中查找到员工编号、发工资的日期、当前工资

    select
    emp_no,
    to_date,
    salary
    from salaries
    where to_date = '9999-01-01'

3.计算在职员工的薪资涨幅：growth

n.salary - o.salary as growth

4.将入职工资表和当前工资表按照员工编号emp_no相等的标准连接，并从里面查找emp_no和growth

select
    n.emp_no,
    n.salary - o.salary as growth
from(
    select 
    s.emp_no, s.salary 
    from salaries s 
    join employees e on e.hire_date = s.from_date
) o
# 入职工资 Onboarding
join(
    select
    emp_no,
    to_date,
    salary
    from salaries
    where to_date = '9999-01-01'
) n
# 当前工资 now
    on o.emp_no = n.emp_no
order by
    growth

完整代码如下：

select
    n.emp_no,
    n.salary - o.salary as growth
from(
    select 
    s.emp_no, s.salary 
    from salaries s 
    join employees e on e.hire_date = s.from_date
) o
# 入职工资 Onboarding
join(
    select
    emp_no,
    to_date,
    salary
    from salaries
    where to_date = '9999-01-01'
) n
# 当前工资 now
    on o.emp_no = n.emp_no
order by
    growth

完结