方法）三表连结分组

SELECT d.dept_no, d.dept_name, t.title, COUNT(t.emp_no)
FROM departments AS d, dept_emp AS de, titles AS t
WHERE d.dept_no = de.dept_no
AND t.emp_no = de.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 d.dept_no;

21/3/25 更新

方法2）简化代码，用窗口函数count()over()解决

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