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

with count_titles_emp as (
    select 
        d_e.dept_no,
        t.title,
        count(t.title) as count
    from  
        dept_emp as d_e
    left join 
        titles as t
    on 
        d_e.emp_no = t.emp_no
    group by 
        d_e.dept_no,
        t.title
)

select 
    cte.dept_no,
    d.dept_name,
    cte.title,
    cte.count
from 
    count_titles_emp as cte
left join 
    departments as d
on 
    cte.dept_no = d.dept_no
order by 
    cte.dept_no,
    cte.title