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

select distinct t.title, avg(s.salary) over (partition by t.title) as `avg(s.salary)`
from titles as t inner join salaries s
  on t.emp_no = s.emp_no
order by `avg(s.salary)`;

select s.emp_no, t.title, s.salary
from titles as t
       left join salaries as s
                 on t.emp_no = s.emp_no
where t.to_date = '9999-01-01'
and s.to_date = '9999-01-01';

select title, avg(salary)
from (select t.title, s.salary
    from titles as t
             left join salaries as s
                       on t.emp_no = s.emp_no
    where t.to_date = '9999-01-01'
      and s.to_date = '9999-01-01') as a1
group by title;