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

答案

select de.dept_no, d.dept_name, t.title, count(t.title)
from dept_emp de, titles t, departments d
where de.emp_no = t.emp_no
and d.dept_no = de.dept_no
and de.to_date = '9999-01-01'
group by de.dept_no, t.title
order by dept_no;

思路

1. 连接dept_emp, titles表，查询 dept_no, title, title对应的数目count；使用group by，先后按照dept_no和title分组

   select de.dept_no, t.title, count(t.title)
   from dept_emp de, titles t
   where de.emp_no = t.emp_no
   and de.to_date = '9999-01-01'
   group by de.dept_no, t.title;

2. 连接1和departments表，查询 dept_no, dept_name, title, title对应的数目count
3. 按照dept_no升序排序

优化

看到评论里都把t.to_date='9999-01-01'放在筛选条件中，另外group by的时候把dept_name也加入分组，这样处理更加稳妥。

复制高排名答案，来自@Erica_Wang

select d.dept_no, d.dept_name, t.title, count(t.title)
from departments d, dept_emp de, titles t
where d.dept_no = de.dept_no and de.emp_no = t.emp_no
and de.to_date = '9999-01-01' and t.to_date = '9999-01-01'
group by d.dept_no, d.dept_name, t.title
order by dept_no