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

            FROM salaries
            WHERE to_date = '9999-01-01')
   , sa AS (SELECT aa.emp_no,
                   MAX(from_date) OVER (PARTITION BY s.emp_no) ssa,
                   MIN(from_date) OVER (PARTITION BY s.emp_no) ssb
            FROM salaries s
                     JOIN aa ON aa.emp_no = s.emp_no)
   , sb AS (SELECT sa.emp_no, s.salary
            FROM salaries s
                     LEFT JOIN sa ON ssa = from_date)
   , sc AS (SELECT sa.emp_no, s.salary
            FROM salaries s
                     LEFT JOIN sa ON ssb = from_date)
   , sd AS (SELECT sc.emp_no, (sb.salary - sc.salary) AS growth
            FROM sc
                     LEFT JOIN sb ON sc.emp_no = sb.emp_no)
SELECT e.emp_no, growth
FROM employees e
         JOIN sd ON e.emp_no = sd.emp_no
GROUP BY e.emp_no, growth
ORDER BY growth;

• 这个是我***似的解法，完全就是没有充分利用第一个表，就是先判断出来所有未离职的，
• 然后查询出来最大的入职日期，和最初的入职日期，
• 然后就很简单的减法
• 然
• 然
• 然而当我进评论区的时候发现，我屮艸芔茻，我真的好傻
• • 接下来我要放大招了

inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date

inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'

order by growth ;

• 这个大佬就是很简单就写出来了,用employees表中的入职日期关联salaries,
• 用离职的日期9999,查询薪资