取出包含某几个指定字符的列

with tab as(
select 111 name,'aaa' subject from dual union all
select 111,'bbb' from dual union all
select 111,'ccc' from dual union all
select 222,'aaa' from dual union all
select 222,'bbb' from dual union all
select 222,'ccc' from dual union all
select 333,'ccc' from dual union all
select 333,'bbb' from dual union all
select 333,'aaa' from dual union all
select 333,'ddd' from dual union all
select 333,'eee' from dual union all
select 444,'aaa' from dual union all
select 444,'bbb' from dual union all
select 444,'eee' from dual union all
select 444,'fff' from dual
)
select name,subject
from tab
where name in(select name
              from tab 
              where subject in('aaa','bbb','ccc')
              group by name 
              having count(*)=3)
--1、取出subject字段下仅含aaa,bbb,ccc的数据
select a.* from tab a where exists(
    select 1 from (
        select name ,replace(max(wm_subject),',','') wm_subject from(
               select name,
                      wm_concat(subject)over(partition by name order by subject ) wm_subject 
               from tab  
        ) 
        group by name
    ) b
    where a.name=b.name and replace(b.wm_subject,'aaa'||'bbb'||'ccc') is null
)
--2、取出subject字段下含有aaa,bbb,ccc的数据
--解法1：
select a.* from tab a where exists(
    select 1 from (
        select name ,replace(max(wm_subject),',','') wm_subject from(
               select name,
                      wm_concat(subject)over(partition by name order by subject ) wm_subject 
               from tab  
        ) 
        group by name
    ) b
    where a.name=b.name and instr(b.wm_subject,'aaa'||'bbb'||'ccc')>0
)
--解法2：
select name,subject
from tb
where name in(select name
              from tb 
              where subject in('aaa','bbb','ccc')
              group by name 
              having count(*)=3)

原帖：http://topic.csdn.net/u/20110322/09/f10e9f27-4fbd-45d8-aef2-eef53cb478f4.html?29716