# 思路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