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

with tmp as (select de.emp_no,dm.dept_no,dm.dept_name,ti.title,count(ti.title) a
            from dept_emp de 
            left join departments dm on de.dept_no = dm.dept_no
            left join titles ti on de.emp_no = ti.emp_no
            where ti.to_date = '9999-01-01'
            and de.to_date = '9999-01-01'
             group by ti.title,dm.dept_no
            order by dm.dept_no asc)
select dept_no,dept_name,title,a as count
from tmp