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

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.youkuaiyun.com/u/20110322/09/f10e9f27-4fbd-45d8-aef2-eef53cb478f4.html?29716