题解 | #统计出当前各个title类型对应的员工当前薪水对应的平均工资#

select hire_salary.emp_no,current_salary-hire_salary as growth from

(
    select salaries.salary as hire_salary,salaries.emp_no as emp_no 
    from salaries,
        (
            select hire_date,emp_no
            from employees
            where emp_no in(
                select emp_no
                from salaries
                where salaries.to_date='9999-01-01'
                group by emp_no
            ) 
        )as hire_info
    where salaries.emp_no=hire_info.emp_no and from_date = hire_Date
)
as hire_salary,

(
    select salaries.salary as current_salary,emp_no
    from salaries
    where salaries.to_date='9999-01-01'
    group by emp_no
)
as current_salary

where hire_salary.emp_no= current_salary.emp_no order by growth