题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
拆分成两个表格,一个表格是在职员工的初始工资表 t1,一个表格是在职员工的最终工资 t2,这两个表格的主键均为emp_no,最后用 t2 减去 t1 即可
SELECT
t1.emp_no as emp_no,t2.salary - t1.salary as growth
FROM
(SELECT s.emp_no,s.salary,e.hire_date
FROM salaries s LEFT JOIN employees e ON s.emp_no = e.emp_no
WHERE s.from_date = e.hire_date AND s.emp_no IN
(SELECT emp_no FROM salaries WHERE to_date = '9999-01-01'))t1 LEFT JOIN
(SELECT emp_no,salary,to_date
FROM salaries
WHERE to_date = '9999-01-01')t2 ON t1.emp_no = t2.emp_no
ORDER BY growth

