查找在职员工自入职以来的薪水涨幅情况,给出在职员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序,以上例子输出为(注: to_date为薪资调整某个结束日期,或者为离职日期,to_date='9999-01-01'时,表示依然在职,无后续调整记录)
思路:
t1:查询出目前还在职的员工和目前薪资
select emp_no,salary from salaries where to_date = '9999-01-01' GROUP BY emp_no
t2:查询员工刚入职时的薪资,好像这道题默认这个值为薪资最小值,所以就稍微容易些
select emp_no, min(salary) as min_Salary from salaries GROUP BY emp_no
然后将t1、t2通过两边的emp_no连接起来,可以排除掉离职的员工。使用emp_no和salary一起输出可以使两者对应起来,避免再次出现题SQL12那样数据对应不上的错误
最后按照growth进行升序:order by growth
答案:
select t1.emp_no,(t1.salary-t2.min_Salary) growth from (select emp_no,salary from salaries where to_date = '9999-01-01' GROUP BY emp_no) t1 join (select emp_no, min(salary) as min_Salary from salaries GROUP BY emp_no) t2 on (t1.emp_no = t2.emp_no) order by growth
运行结果: