三种写法
# 3表连接 -- 按员工分组,得到title,然后计算count select de.dept_no, d.dept_name, title, count(distinct title) from dept_emp as de inner join titles t on de.emp_no=t.emp_no inner join departments d on de.dept_no=d.dept_no where de.to_date='9999-01-01' and t.to_date='9999-01-01' group by de.emp_no -- 按员工 having count(distinct title) order by de.dept_no # 法2 -- 按照d.dept_no部门、title分组 select de.dept_no, d.dept_name, title, count(distinct title) from dept_emp as de inner join titles t on de.emp_no=t.emp_no inner join departments d on de.dept_no=d.dept_no where de.to_date='9999-01-01' and t.to_date='9999-01-01' group by d.dept_no, title having count(distinct title) order by de.dept_no # 法3 开窗函数 -- count(t.emp_no) over(parittion by ...) select distinct d.dept_no, d.dept_name, t.title, count(t.emp_no)over(partition by de.emp_no, t.title ) 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;