题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
1.查出未离职员工编号
2.根据编号用窗口函数first_value()查出入职、离职工资
3.做差、排序
SELECT
a.emp_no
,a.离职工资-入职工资 growth
FROM (SELECT
emp_no
,FIRST_VALUE(salary)over(partition by emp_no order by to_date) 入职工资
,FIRST_VALUE(salary)over(partition by emp_no order by to_date desc) 离职工资
from salaries
where emp_no IN (select emp_no from salaries where to_date='9999-01-01')
) a
GROUP BY a.emp_no
order by growth