题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
- SELECT
- s3.emp_no,
- s3.salary2-s3.salary1 AS growth
- FROM
- ((SELECT
-
e.emp_no,
-
s.salary as salary1
- FROM
-
employees e
-
LEFT JOIN salaries s
-
ON e.emp_no = s.emp_no
- WHERE e.hire_date = s.from_date) AS s1 -- 入职薪水
- INNER JOIN
-
(SELECT
-
e.emp_no,
-
s.salary as salary2
-
FROM
-
employees e
-
LEFT JOIN salaries s
-
ON e.emp_no = s.emp_no
-
WHERE s.to_date = '9999-01-01') AS s2 -- 现在薪水
-
ON s1.emp_no = s2.emp_no ) AS s3
- ORDER BY growth 我一开始一直无法理解别人的,自己写了一个,跟其他人的区别就是我把S1跟S2内连接后当作S3,最后所有的select都从S3取,我觉得更适合新手理解。