题解 | #汇总各个部门当前员工的title类型的分配数目#
汇总各个部门当前员工的title类型的分配数目
http://www.nowcoder.com/practice/4bcb6a7d3e39423291d2f7bdbbff87f8
没用group by
select distinct de.dept_no , dep.dept_name , t.title , (select count(title) from titles t1 where
emp_no in
(select emp_no from dept_emp d1 where d1.dept_no = de.dept_no)
and t1.title =t.title )
from dept_emp de join departments dep using(dept_no)
join titles t using(emp_no) order by dept_no , title;
用了group by
select de.dept_no,de.dept_name, title ,count(t.title) as count from titles t
join dept_emp using(emp_no)
join departments de using(dept_no)
group by de.dept_no,t.title
order by dept_no