题解 | #汇总各个部门当前员工的title类型的分配数目#

-- 窗口函数count，注意distinct 窗口函数不聚合故要distinct
 SELECT
 distinct de.dept_no, dp.dept_name, t.title, count(t.title) over(partition by de.dept_no,t.title order by t.title )
 FROM
 departments dp,dept_emp de,titles t
 where dp.dept_no = de.dept_no
 and de.emp_no = t.emp_no
 order by de.dept_no

-- 法2 联立三表，然后按（dept_no, title）分组
select d.dept_no,
       max(d.dept_name),
       t.title,
       count(t.title)as count
from departments d,dept_emp de,titles t
where de.emp_no=t.emp_no
and de.dept_no=d.dept_no
and de.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by d.dept_no,t.title
order by d.dept_no, t.title