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

with t1 as (
    select a.emp_no, a.dept_no, b.dept_name
    from dept_emp as a
    left join departments as b
    on a.dept_no = b.dept_no
)
select t1.dept_no, t1.dept_name, c.title, count(title) as count
from t1
left join titles as c
on t1.emp_no = c.emp_no
group by t1.dept_no, t1.dept_name, c.title
order by t1.dept_no, c.title;