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

with
    tb AS 
    (
        SELECT
            de.emp_no,
            de.dept_no,
            ti.title
        FROM
            dept_emp de
            INNER JOIN titles ti ON de.emp_no = ti.emp_no
        WHERE
            de.to_date = '9999-01-01'
    )
SELECT
    dep.dept_no,
    dep.dept_name,
    tb.title,
    count(title)
FROM
    departments dep
    INNER JOIN tb ON tb.dept_no = dep.dept_no
GROUP BY
    tb.title, dep.dept_no 
ORDER BY
    dep.dept_no,
    tb.title;