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

#各个部门/当前员工/title的数组

with cur as #在职员工，含部门名称,含职称
(
select
e.emp_no as emp_no
,e.dept_no as dept_no
, d.dept_name as dept_name
,t.title as title
, count(e.emp_no)over(partition by e.dept_no, t.title) as cnt
from dept_emp e left join departments d
on e.dept_no = d.dept_no
left join titles t
on e.emp_no=t.emp_no
where e.to_date='9999-01-01' and t.to_date='9999-01-01'
)

select
dept_no, dept_name, title, cnt #已经distinct过了
from cur
group by dept_no, dept_name, title, cnt 
order by dept_no asc, title asc