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

【新知识】group by 可以嵌套使用，之前一直以为只能对一个条件group
【代码】
select t2.dept_no, dp.dept_name, t2.title, t2.count from
(select t1.dept_no, t1.title, count(title) as count from
(select t.emp_no, de.dept_no, t.title from dept_emp as de right join titles as t on t.emp_no = de.emp_no) as t1
group by dept_no, title) as t2
left join
departments as dp
on t2.dept_no = dp.dept_no
order by dept_no
预期输出：
d001|Marketing|Senior Engineer|1
d001|Marketing|Staff|1
d002|Finance|Senior Engineer|1
d003|Human Resources|Senior Staff|1
d004|Production|Senior Engineer|2
d005|Development|Engineer|1
d005|Development|Senior Staff|1
d006|Quality Management|Engineer|1
d006|Quality Management|Senior Engineer|1
实际输出：
d001|Marketing|Senior Engineer|1
d001|Marketing|Staff|1
d002|Finance|Senior Engineer|1
d003|Human Resources|Senior Staff|1
d004|Production|Senior Engineer|2
d005|Development|Senior Staff|1
d005|Development|Engineer|1
d006|Quality Management|Senior Engineer|1
d006|Quality Management|Engineer|1
【修改代码】
select t2.dept_no, dp.dept_name, t2.title, t2.count from
(select t1.dept_no, t1.title, count(title) as count from
(select t.emp_no, de.dept_no, t.title from dept_emp as de right join titles as t on t.emp_no = de.emp_no) as t1
group by dept_no, title) as t2
left join
departments as dp
on t2.dept_no = dp.dept_no
order by dept_no, title（增加了一个按照title排序即可）