SQL 21) 查找所有员工自入职以来的薪水涨幅情况

方法）利用子查询找出当前工资及入职工资

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;