题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
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取，我觉得更适合新手理解。
查看1道真题和解析