题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
第一步：建一个包含emp_no和salary的在职员工临时表 a （这个表里的员工的salary都是最新的salary）
select emp_no,salary as last_salary from salaries where to_date = '9999-01-01'
第二步：建一个包含emp_no和salary的员工临时表b（这个表里的员工的salary都是刚入职时的员工salary）
select emp_no,salary as first_salary
from salaries
where (emp_no,from_date) in (select emp_no,min(from_date)
from salary
group by emp_no)第三步：联结两个表
select a.emp_no,(a.last_salary-b.first_salary) as growth
from (select emp_no,salary as last_salary
from salaries
where to_date = '9999-01-01') a
left join
(select emp_no,salary as first_salary
from salaries
where (emp_no,from_date) in (select emp_no,min(from_date)
from salaries
group by emp_no))b
on a.emp_no=b.emp_no
order by growth 
查看6道真题和解析
