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

select
    p.dept_no,
    p.dept_name,
    ak.title,
    count(ak.title) count
from
    (
        select
            t.emp_no,
            d.dept_no,
            t.title
        from
            titles t
            join dept_emp d on t.emp_no = d.emp_no
        where
            t.to_date = '9999-01-01'
            and d.to_date = '9999-01-01'
    ) ak
    join departments p on ak.dept_no = p.dept_no
group by
    p.dept_no,
    p.dept_name,
    ak.title
order by
    p.dept_no,
    ak.title