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

# 方法一，多表直接连接查询
# select de.dept_no, dept_name, title, count(t.emp_no) count
# from departments d, dept_emp de, titles t
# where d.dept_no = de.dept_no and de.emp_no = t.emp_no and de.to_date = '9999-01-01' and t.to_date = '9999-01-01'
# group by dept_no, title
# order by dept_no asc, title asc;

# 方法二，join多次连接查询
# select d.dept_no, dept_name, title, count(t.emp_no) count
# from departments d
# join dept_emp de on d.dept_no = de.dept_no and de.to_date = '9999-01-01'
# join titles t on de.emp_no = t.emp_no and t.to_date = '9999-01-01'
# group by dept_no, title
# order by dept_no asc, title asc;

# 方法三，join多层连接查询
# select dept_no, dept_name, title, count(t.emp_no) count
# from titles t
# join (
#     select d.dept_no, d.dept_name, de.emp_no
#     from departments d
#     join dept_emp de on d.dept_no = de.dept_no and de.to_date = '9999-01-01'
# ) r on t.emp_no = r.emp_no and t.to_date = '9999-01-01'
# group by dept_no, title
# order by dept_no asc, title asc;

# 方法四，窗口函数 + partition by
select distinct d.dept_no, dept_name, t.title, count(t.emp_no) over(partition by dept_no, title)
from departments as d 
join dept_emp as de on d.dept_no = de.dept_no
join titles as t on t.emp_no = de.emp_no
where de.to_date = '9999-01-01' and t.to_date = '9999-01-01'
order by dept_no;