题解 | #查找在职员工自入职以来的薪水涨幅情况#

题解说明

• 没有用到员工表employees 薪水表salaries三次自连接完成
• 可用于实现所有员工的入职薪资与最近薪资比较（对于离职员工即最后薪资）

完整语句

SELECT
	t.emp_no,
	s2.salary - s1.salary growth 
FROM
	salaries s1
	JOIN salaries s2 ON s1.emp_no = s2.emp_no
	JOIN ( SELECT emp_no, min( from_date ) min, max( to_date ) max FROM salaries GROUP BY emp_no ) t ON t.emp_no = s2.emp_no 
WHERE
	s1.emp_no = t.emp_no 
	AND s1.from_date = min 
	AND s2.to_date = max 
	AND s2.to_date = '9999-01-01' 
ORDER BY
	growth

分解思路说明

1. 从薪水表salaries中 找到每个员工的薪资最早记录日期(min(from_date)) & 最晚薪资记录日期(max(to_date))

SELECT emp_no, min( from_date ) min, max( to_date ) max 
FROM salaries 
GROUP BY emp_no

2. 自连接薪水表salaries

salaries s1 JOIN salaries s2 ON s1.emp_no = s2.emp_no

3. 查找各员工号那一条s1包含最早薪资和s2包含最近薪资的数据

WHERE
	s1.emp_no = t.emp_no
    AND s1.from_date = min
    AND s2.to_date = max 

4. 查找题设所需数据

SELECT
    t.emp_no,
    s2.salary - s1.salary growth 

注意小细节

• 升序排列

ORDER BY
    growth

• 在职员工

AND s2.to_date = '9999-01-01'