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

解题思路

#第一步：链接表departments,dept_emp,获取dept_no,dept_name

select d1.dept_no,d1.dept_name,d2.emp_no
from departments as d1
inner join dept_emp as d2
on d1.dept_no=d2.dept_no
where d2.to_date='9999-01-01';


#第二步：获取titles表中的title
select emp_no,title 
from titles 
where to_date='9999-01-01';

#第三步：链接上面两表
select a1.dept_no,a1.dept_name,a2.title,count(a2.title) as count
from 
(
    select d1.dept_no,d1.dept_name,d2.emp_no,d2.from_date,d2.to_date
    from departments as d1
    inner join dept_emp as d2
    on d1.dept_no=d2.dept_no
    where d2.to_date='9999-01-01') as a1

inner join 

(
    select emp_no,title,from_date,to_date 
    from titles 
    where to_date='9999-01-01')    as a2    
on a1.emp_no=a2.emp_no
where a2.to_date='9999-01-01'
group by a1.dept_no,a1.dept_name,a2.title
order by a1.dept_no;    

#注意1：记得添加过滤条件where a2.to_date='9999-01-01'
#注意2：只按部门分组，不符合题意输出结果，要添加多条件分组

实现代码

select a1.dept_no,a1.dept_name,a2.title,count(a2.title) as count
from 
(
    select d1.dept_no,d1.dept_name,d2.emp_no,d2.from_date,d2.to_date
    from departments as d1
    inner join dept_emp as d2
    on d1.dept_no=d2.dept_no
    where d2.to_date='9999-01-01') as a1

inner join 

(
    select emp_no,title,from_date,to_date 
    from titles 
    where to_date='9999-01-01')    as a2    
on a1.emp_no=a2.emp_no
where a2.to_date='9999-01-01'            
group by a1.dept_no,a2.title   
order by a1.dept_no;