题解 | #写了三种解法 然后各不相同吧 感觉SQL的技术含量就在join over groupby 这三个里面了#

select emp_no,mas-mis as qq from
# 开窗函数 在旁边加最大to日期、最小from日期、最大工资、最小工资
# 如果最后不需要识别尚在我司员工（用于判断growth = 0时显不显示） 则不需要最大to日期、最小from日期
(select *,( max(to_date) over (partition by emp_no) ) as td,
          ( min(from_date) over (partition by emp_no) ) as fd, 
          ( min(salary) over (partition by emp_no) ) as mis, 
          ( max(salary) over (partition by emp_no) ) as mas
 from salaries) as t1
#  挑选离职或最新工资 以及初始工资 并且要求growth > 0 或者 员工尚在我司
 where (to_date=td or from_date = fd) and (mas-mis>0 or td = '9999-01-01')
#  去掉重复列 前面用distinc(*)也行
# 重复的原因是我们实际没有考虑每条记录的salary值 而是直接生成了最大最小列 所以一个人有多少条他就有几次重复
# 我们只考虑了每条的日期
 group by emp_no
 order by qq

# select e1,growth from
# (SELECT e1,mas-mis as growth from
# (select * from
#     (select emp_no as e1 , max(salary) as mas from salaries group by emp_no) as t1
# left join 
# (select emp_no as e2 , min(salary) as mis from salaries group by emp_no) as t2
# on e1 = e2) as t3) as t6
# left join 
# (select emp_no as e3, max(to_date) as dd from salaries group by emp_no) as t4
# on e1 = e3
# where growth > 0 or dd = '9999-01-01'
# order by growth




# select * from
# (select * from
# (salaries left join 
# (select emp_no as e1,max(to_date) as td from salaries group by emp_no) as t1
# on t1.e1 = salaries.emp_no)) as t3
# where to_date = td 

# union
# select * from
#  (select * from
# (salaries left join 
#  (select emp_no as e2,min(from_date) as fd from salaries group by emp_no) as t2
#  on emp_no = t2.e2)) as t4