题解 | #多思路#

# 思路1：统计每个用户每次工资变化的涨幅，汇总每个用户的工资涨幅总和
# select emp_no,sum(change_salary) growth
# from (
#   select *
#   ,salary-lag(salary)over(partition by emp_no order by from_date) change_salary
#   from salaries
#   where emp_no in (
#     select emp_no
#     from salaries
#     where to_date='9999-01-01'
# )
# ) as t1
# group by emp_no
# order by growth

# 思路2：当前工资-入职工资
# with now as (
#   SELECT emp_no,salary
#   from salaries
#   where (emp_no,from_date) in (
#     SELECT emp_no,max(from_date)
#     from salaries
#     GROUP by emp_no
#   )
#   and to_date='9999-01-01'
# ), -- 当前工资表
# hire as (
#   SELECT e.emp_no,salary
#   from employees e
#   inner join salaries s
#   on e.emp_no=s.emp_no and hire_date=from_date
# ) -- 入职工资表
# select now.emp_no,(now.salary-hire.salary) growth
# from now
# inner join hire
# on now.emp_no=hire.emp_no
# order by growth