##感觉这个比较简单
select r1.dept_no,d.dept_name ,r1.title,count(1) from (select de.emp_no,de.dept_no,t.title from dept_emp de inner join titles t on de.emp_no = t.emp_no)r1 left join departments d on r1.dept_no=d.dept_no group by r1.dept_no,r1.title,d.dept_name order by dept_no asc