```sql
select dep.dept_no,dep.dept_name,a.title,count(a.title)
from departments as dep inner join
(select de.dept_no,de.emp_no,t.title
from dept_emp as de inner join titles as t on t.emp_no=de.emp_no) as a
on dep.dept_no =a.dept_no
group by dep.dept_no,a.title
order by dep.dept_no ;
1.首先两表dept_emp和titles 做内连接，生成新表（column为 dept_no，emp_no,title）
2.与departments表在做内连接，生成所需表，查询内容为题干内容，最后加上分组汇总函数group by
与排序 orderby