SQL 20) 统计各个部门的工资记录数

方法）三表连结再分组使用聚合函数

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;