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

select
dep.dept_no dept_no,
dep.dept_name dept_name,
s1.title title,
count(s1.title) cout
from
departments dep join 
(select 
de.dept_no dept_no,ti.title title
from 
dept_emp de join titles ti on de.emp_no = ti.emp_no
 where de.to_date = '9999-01-01'
 and ti.to_date = '9999-01-01'
 order by ti.title
)as s1
on dep.dept_no = s1.dept_no
GROUP BY dep.dept_no, dep.dept_name, s1.title
order by dep.dept_no