题解 | #获取每个部门中当前员工薪水最高的相关信息#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
注意审题是【在职员工】所以只用考虑to_date='9999-01-01'的情况。
可以用最后的工资减去入职时的工资即可。
如果是所有员工包括离职的。
select hire.emp_no, last.last_salary-hire.salary as growth from (select s.emp_no,salary from employees e INNER JOIN salaries s on e.emp_no= s.emp_no and e.hire_date = s.from_date) hire INNER JOIN (select s.emp_no,salary as last_salary from salaries s where to_date='9999-01-01' ) last on hire.emp_no = last.emp_no order by growth
如果是所有员工包括离职的。
select hire.emp_no, last.last_salary-hire.salary as growth from (select s.emp_no,salary from employees e INNER JOIN salaries s on e.emp_no= s.emp_no and e.hire_date = s.from_date) hire INNER JOIN (select s.emp_no,salary as last_salary from salaries s INNER JOIN( select emp_no,max(to_date) as last_date from salaries group by emp_no) t on t.emp_no = s.emp_no and s.to_date = t.last_date) last on hire.emp_no = last.emp_no order by growth
阿里云工作强度 702人发布