题解 | 简单相关子查询解题#查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
SELECT s.emp_no, (s.salary - ( SELECT salary FROM salaries JOIN employees USING(emp_no) WHERE hire_date = from_date AND emp_no = s.emp_no )) AS growth FROM salaries s WHERE to_date = '9999-01-01' ORDER BY growth
直接在SELECT后面加入一个子查询即可。
思路：首先找出在职员工
SELECT s.emp_no FROM salaries s WHERE to_date = '9999-01-01' ORDER BY growth在职员工找出则代表其现在的薪酬即s.salary已经找出来，接下来要做的就是找出员工入职时的薪酬
SELECT salary FROM salaries JOIN employees USING(emp_no) WHERE hire_date = from_date最后将两者通过emp_no关联起来即可，即 emp_no = s.emp_no；同时将现在的薪酬与入职时的薪酬相减，即s.salary-()