方法)三表连结再分组使用聚合函数
SELECT d.dept_no, d.dept_name, COUNT(s.emp_no) FROM dept_emp AS de, departments AS d, salaries AS s WHERE de.dept_no = d.dept_no AND s.emp_no = de.emp_no GROUP BY d.dept_no, d.dept_name ORDER BY d.dept_no ASC;
21/3/25 更新
方法2)第二次做发现另一种方法,使用窗口函数count()over()
select distinct d.dept_no , d.dept_name, count(s.salary)over(partition by d.dept_no order by d.dept_no) from departments as d join dept_emp as de on d.dept_no = de.dept_no join salaries as s on s.emp_no = de.emp_no;