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

# 思路：GROUP BY title
# 做两表的连接，departments表与demp_emp表的链接
# ORDER BY dept_no,title
# 这里的一个主要问题是only_full_group_by的限制，而本题暗含以dept_no 和 title共同分组，所以先用这两列做分组聚合
SELECT
Temp2.dept_no,departments.dept_name,Temp2.title,Temp2.count
FROM
departments LEFT JOIN 
(SELECT
dept_no,title,COUNT(*) AS count
FROM
(SELECT 
Temp.dept_no,Temp.dept_name,Temp.emp_no,titles.title
FROM
(SELECT
departments.dept_no,departments.dept_name,dept_emp.emp_no
FROM
departments LEFT JOIN dept_emp
ON
departments.dept_no  = dept_emp.dept_no) AS Temp
LEFT JOIN 
titles 
ON 
Temp.emp_no = titles.emp_no ) AS Temp1
GROUP BY
title,dept_no ) AS Temp2
ON
departments.dept_no = Temp2.dept_no
ORDER BY Temp2.dept_no,Temp2.title