题解 | #汇总各个部门当前员工的title类型的分配数目#

本题知识点：

• group by a,b是将a与b合在一起进行分组

• order by a,b是按照a排序如果a相同再按照b排序 首先按照部门以及title进行分组，然后join部门表，最后进行排序，代码如下：

select
  b.dept_no as dept_no,
  a.dept_name as dept_name,
  b.title as title,
  b.cnt as ` count `
from
  departments a
  inner join (
    select
      a.dept_no as dept_no,
      c.title as title,
      count(*) as cnt
    from
      (
        departments a
        inner join dept_emp b on a.dept_no = b.dept_no
        inner join titles c on b.emp_no = c.emp_no
      )
    where
      b.to_date = '9999-01-01'
      and c.to_date = '9999-01-01'
    group by
      a.dept_no,
      c.title
  ) as b on a.dept_no = b.dept_no
  order by
  b.dept_no,
  b.title;