题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
一种简洁的写法
SELECT a.emp_no, b.salary - c.salary AS growth FROM employees AS a, salaries AS b, salaries AS c WHERE a.emp_no = b.emp_no AND b.to_date = "9999-01-01" AND a.emp_no = c.emp_no AND c.from_date = a.hire_date ORDER BY growth
一种通俗些的写法
SELECT a.emp_no AS emp_no,
a.max_salary - b.min_salary AS growth
FROM
(
SELECT salary AS max_salary,
emp_no
FROM salaries
WHERE to_date = "9999-01-01"
) AS a
LEFT JOIN
(
SELECT salary as min_salary,
e.emp_no
FROM salaries AS e, employees AS e1
WHERE e.emp_no = e1.emp_no AND e1.hire_date = e.from_date
) AS b
ON a.emp_no = b.emp_no
ORDER BY growth
