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

select dept_no, dept_name, title, count
from
(
    select distinct dept_name, title, dept_no, count(title) over(partition by dept_no, title) as count
    from
    (
        select dt.dept_no, dept_name, title
        from
        (
            select de.emp_no, tl.title, de.dept_no
            from 
            dept_emp as de join titles as tl
            on de.emp_no=tl.emp_no
        ) as t1
        join departments as dt
        on t1.dept_no=dt.dept_no
    ) as t2
) as t3