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

思想:

1. 求出每个员工的to_date值为最大值的员工信息
2. 求出每个员工的to_date值为最小值的员工信息
3. 求出每个员工最晚日期的薪水
4. 求出每个员工最早日期的薪水
5. 求出每个员工的编号和涨薪(结果)

代码:

select maxSalary.emp_no,maxSalary.salary-minSalary.salary as growth FROM
(SELECT salaries.emp_no,salaries.salary FROM salaries,(SELECT emp_no,MAX(to_date) AS to_date FROM salaries GROUP BY emp_no) AS et
WHERE salaries.emp_no=et.emp_no AND salaries.to_date=et.to_date and curdate()<salaries.to_date) as maxSalary,
(SELECT salaries.emp_no,salaries.salary FROM salaries,(SELECT emp_no,Min(to_date) AS to_date FROM salaries GROUP BY emp_no) AS et
WHERE salaries.emp_no=et.emp_no AND salaries.to_date=et.to_date)as minSalary
where maxSalary.emp_no=minSalary.emp_no
order by growth ASC

提醒:注意求的是在职员工的涨薪情况

NOW() 可以用来返回当前日期和时间 格式：YYYY-MM-DD hh:mm:ss
CURDATE() 可以用来返回当前日期 格式：YYYY-MM-DD
CURTIME() 可以用来返回当前时间 格式：hh:mm:ss