思路:
- 先用 INNER JOIN 连接 dept_emp 与 salaries,根据测试数据添加限定条件 de.to_date =
‘9999-01-01’ AND t.to_date = ‘9999-01-01’,即当前员工的当前头衔 - 再用 INNER JOIN 连接departments,限定条件为 de.dept_no = dp.dept_no,即部门编号相同
- 最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组,用 COUNT(t.title)
统计相同部门下相同头衔的员工个数
SELECT
de.dept_no,
dp.dept_name,
t.title,
COUNT( t.title ) AS count
FROM
titles AS t
INNER JOIN dept_emp AS de ON t.emp_no = de.emp_no
AND de.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no
GROUP BY
de.dept_no,
t.title