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

分3步

第一步

查出员工表所有的title，并对title进行分组

select ti.title
from titles ti
group by ti.title

第二步

查出员工在薪水表的的平均工资，并对title进行分组

select ti.title, avg(sa.salary) avgSalary
from titles ti
left join salaries sa on ti.emp_no = sa.emp_no
group by ti.title

第三步

将第一步第二步汇总，并排序

select t1.title, t2.avgSalary
from (select ti.title
      from titles ti
      group by ti.title) t1
         left join (select ti.title, avg(sa.salary) avgSalary
                    from titles ti
                             left join salaries sa on ti.emp_no = sa.emp_no
                    group by ti.title) t2
                   on t1.title = t2.title
order by t2.avgSalary