【Oracle】查询表总某列是否全部满足条件*例子详解

01.初始表与问题

X区域,共有A,B,C3种上岗证,如果一个人有这3张上岗证,就判断为AB全能
L区域,共有C,D2种上岗证,如果一个人有这2张上岗证,就判断为CD全能

--全能上岗证
select type,post,Almighty from
(select 'X区域'type,'A证'post,'AB全能'Almighty from dual union all
select 'X区域'type,'B证'post,'AB全能'Almighty from dual union all
select 'X区域'type,'C证'post,'AB全能'Almighty from dual union all
select 'L区域'type,'C证'post,'CD全能'Almighty from dual union all
select 'L区域'type,'D证'post,'CD全能'Almighty from dual)AL_post

工号为01的员工,在X区域有3张上岗证,在L区域有一张上岗证
工号为02的员工,在X区域有1张上岗证

--工号对应的上岗证
select userid,type,post from
(select '01'userid,'X区域'type,'A证'post from dual union all
select '01'userid,'X区域'type,'B证'post from dual union all
select '01'userid,'X区域'type,'C证'post from dual union all
select '01'userid,'L区域'type,'C证'post from dual union all
select '02'userid,'X区域'type,'A证'post from dual)postData

问题:
如果将AL_post表和postData表相连,得出该岗位全能的员工共有几个?
注:如果缺少1张上岗证,就不算岗位全能

02.整合数据

我们先将AL_post 表整合数据,这样我们就知道AB全能有哪些上岗证,CD全能有哪些上岗证

--全能上岗证2
select type,almighy,'%'||listagg(post, '%') within group(order by post)||'%'post  
from AL_post group by type,almighy

然后再将数据表postData整合,这样我们就知道,某个区域里,该工号下具体有那些上岗证了
注:在使用listagg函数时,必须要进行排序

--工号对应的上岗证2
select type,userid,listagg(post, ',') within group(order by post)post  
from postData group by type,userid

03.结合

最终我们得出,X区域,AB全能的人数为1,其余员工都不符合条件

--整合
--整合
select t1.type,Almighty,count(userid)num from
(select type,userid,listagg(post, ',') within group(order by post)post  
from postData  group by type,userid)t1,
(select type,Almighty,'%'||listagg(post, '%') within group(order by post)||'%'post  
from AL_post group by type,Almighty)t2
where t1.type=t2.type(+) and t1.post like t2.post group by t1.type,Almighty