• 开窗函数
    Mysql 8.0以上才可以。
    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)`;
  • 组合查询
    查询表包含emp_no、title和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;