题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

select s1.emp_no,(s1.salary-s2.salary) growth
from
(select salaries.emp_no,salaries.salary
from salaries
 where to_date="9999-01-01"
)s1,
(select salaries.emp_no,salaries.salary
from salaries,employees where
salaries.from_date=employees.hire_date and salaries.emp_no=employees.emp_no) s2
where s1.emp_no=s2.emp_no ORDER BY growth

这道题不严谨吧，考虑了离职员工就会答案不对，但是题干有没有给出要忽略离职员工，上面的sql是忽略离职员工，s1是当前工资，s2是初始工资
如果不忽略离职员工，应该是

select s1.emp_no,(s1.salary-s2.salary) growth
from
(select salaries.emp_no,salaries.salary
from salaries,(select salaries.emp_no,max(to_date) to_date
              from salaries GROUP BY emp_no) s3 
 where salaries.to_date=s3.to_date and salaries.emp_no=s3.emp_no
)s1,
(select salaries.emp_no,salaries.salary
from salaries,employees where
salaries.from_date=employees.hire_date and salaries.emp_no=employees.emp_no) s2
where s1.emp_no=s2.emp_no ORDER BY growth

s3查询的是员工的to_date和员工工号