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

select
a.d1 as dept_no
,d.dept_name as dept_name
,a.t1 as title
,a.c1 as count
from 
(select
e.dept_no as d1
,t.title as t1
,count(e.emp_no) as c1
from dept_emp e left join titles t
on e.emp_no = t.emp_no
where e.to_date = '9999-01-01'
group by e.dept_no, t.title)  as a
left join departments d
on a.d1=dept_no
order by dept_no asc, title asc