方法)利用子查询找出当前工资及入职工资
SELECT s1.emp_no, (s1.salary - s2.salary)growth FROM (SELECT emp_no, salary -- 当前工资 FROM salaries WHERE to_date = '9999-01-01')s1 JOIN (SELECT s.emp_no, s.salary -- 入职工资 FROM salaries AS s JOIN employees AS e ON e.emp_no = s.emp_no AND e.hire_date = s.from_date)s2 ON s1.emp_no = s2.emp_no ORDER BY growth;
21/3/24
方法2)第二次做发现一个更简单的方法
- 入职工资 即 最低工资
select s1.emp_no, (s2.salary - min(s1.salary)) growth from salaries as s1, salaries as s2 where s1.emp_no = s2.emp_no and s2.to_date = '9999-01-01' group by s1.emp_no order by growth;