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

在弄清楚题目要求后，重写代码，通过，运行时间34ms占用内存6648KB。和提供的答案相比：运行时间 34ms 占用内存6672KB。

另外学到的一点是hire_date的type是date，但是可以和字符转‘9999-01-01’直接比较，无需进行类型转化。

我在postgresql输入 select '2022-01-01'::date = '2022-01-01' 返回True。

with current_employees as (
    select
        emp_no
    from salaries
    group by emp_no
    having max(to_date) = '9999-01-01'
    ),
summary as (
    select
        c.emp_no,
        s2.salary as begin_salary,
        s1.salary as current_salary
    from current_employees c
    join employees e
        on c.emp_no = e.emp_no 
    join salaries s1
        on c.emp_no = s1.emp_no and s1.to_date = '9999-01-01' 
    join salaries s2
        on c.emp_no = s2.emp_no and s2.from_date = e.hire_date
)
select 
    emp_no,
    current_salary - begin_salary as growth
from summary
order by (current_salary - begin_salary)