# 方法1：统计每个用户每次工资变化的涨幅，汇总每个用户的工资涨幅总和
# 1.根据用户分组，对日期先后进行排序，求出每个前一次工资 --窗口函数
# select *
# ,lag(salary)over(partition by emp_no order by from_date) before_salary
# from salaries
# 2.求出每个用户随时间变化的当前工资相较于上一次工资的涨幅 -- 窗口函数lag
# select *
# ,salary-lag(salary)over(partition by emp_no order by from_date) change_salary
# from salaries
# 3.在第2步基础上筛选出在职员工  --子查询
# 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'
# )
# 4.汇总每个用户的工资涨幅总和 -- 分组聚合
# 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：当前工资减去入职工资，即为工资涨幅
# 1.查询出员工入职工资（别忘记筛选出在职员工）
# 方式1 -- 子查询
# （1）借助于employees表
# SELECT emp_no,salary
# from salaries
# where (emp_no,from_date) in (
#   SELECT emp_no,hire_date
#   from employees
# )
# （2）若没有给employees则可以用min
# SELECT emp_no,salary
# from salaries
# where (emp_no,from_date) in (
#   SELECT emp_no,min(from_date)
#   from salaries
#   group by emp_no
# )

# 方式2：连接（若没有employees则不能用该方法）
# SELECT e.emp_no,salary
# from employees e
# inner join salaries s
# on e.emp_no=s.emp_no and hire_date=from_date
# 方式3：窗口函数
# SELECT emp_no,salary
# from (
#   SELECT *
#   ,rank()over(partition by emp_no order by from_date) posn
#   from salaries
# ) as t1
# where posn=1
# 2.求每个员工的当前工资(别忘记筛选出在职员工) --max
# 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'

# 3.每个员工当前工资-入职工资，即工资涨幅 --with或连接
# 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