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

# 连接部门员工和title
# 按部门对title计数
# 连接计数后的部门和名称

# 连接部门员工和title, det
# select
#     de.dept_no as dept_no,
#     t.title as title
# from dept_emp de join titles t on de.emp_no=t.emp_no
# where de.to_date='9999-01-01'

# 按部门对title计数
# select
#     det.dept_no as dept_no,
#     det.title as title,
#     count(*) over(partition by det.title, det.dept_no) as count
# from (
#     select 
#         de.dept_no as dept_no,
#         t.title as title
#     from dept_emp de join titles t on de.emp_no=t.emp_no
#     where de.to_date='9999-01-01'
#     ) det

# 连接计数后的部门和名称，并删除重复值
 select distinct
    det.dept_no as dept_no,
    d.dept_name as dept_name,
    det.title as title,
    count(*) over(partition by det.title, det.dept_no) as count
from (
    select 
        de.dept_no as dept_no,
        t.title as title
    from dept_emp de join titles t on de.emp_no=t.emp_no
    where de.to_date='9999-01-01'
    ) det join departments d on det.dept_no=d.dept_no
    order by dept_no, title