题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
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,查询薪资
