这里是一个选择：

SQL> with
  2  table1 (person,qualities) as
  3    (select 'Joe','5,6,7,8,9' from dual union all
  4     select 'Mary','7,10,15,20' from dual union all
  5     select 'Bob',9,11,12' from dual
  6    ),7  table2 (id,descr) as
  8    (select 5,'Nice' from dual union all
  9     select 6,'Tall' from dual union all
 10     select 7,'Short' from dual
 11    ),12  table3 (id,descr) as
 13    (select 8,'Angry' from dual union all
 14     select 9,'Sad' from dual union all
 15     select 10,'Fun' from dual
 16    ),17  table4 (id,descr) as
 18    (select 11,'Boring' from dual union all
 19     select 12,'Happy' from dual union all
 20     select 15,'Cool' from dual union all
 21     select 20,'Mad' from dual
 22    ),23  t1new (person,id) as
 24    (select person,regexp_substr(qualities,'[^,]+',1,column_value) id
 25     from table1 cross join table(cast(multiset(select level from dual
 26                                                connect by level <= regexp_count(qualities,',') + 1
 27                                               ) as sys.odcinumberlist))
 28    )
 29  select a.person,30        count(b.id) bid,31        count(c.id) cid,32        count(d.id) did
 33  from t1new a left join table2 b on a.id = b.id
 34               left join table3 c on a.id = c.id
 35               left join table4 d on a.id = d.id
 36  group by a.person
 37  having (    count(b.id) > 0
 38          and count(c.id) > 0
 39          and count(d.id) > 0
 40         );

PERS        BID        CID        DID
---- ---------- ---------- ----------
Bob           1          3          2
Mary          1          2          2

SQL>

它是做什么的？

• 第1-22行代表您的示例数据
• T1NEW CTE（第23-28行）将每个人的逗号分隔质量分成几行
• 最后的select（第29-40行）是t1new与每个“描述”表（table2/3/4）的外部联接，并计算其中每个表包含多少个质量人的素质（由t1new中的行表示）
• having子句此处仅返回所需的人；每个计数都必须为正数

也许这会有所帮助： {1}创建一个对所有质量进行分类的视图，并允许您选择质量ID和类别。 {2}将视图联接到TABLE1，并使用联接条件“拆分”存储在TABLE1中的CSV值。

{1}查看

create or replace view allqualities
as
select 1 as category,id as qid,descr from table2
union
select 2,id,descr from table3
union
select 3,descr from table4
;

select * from allqualities order by category,qid ;

  CATEGORY        QID DESCR 
---------- ---------- ------
         1          5 Nice  
         1          6 Tall  
         1          7 Short 
         2          8 Angry 
         2          9 Sad   
         2         10 Fun   
         3         11 Boring
         3         12 Happy 
         3         15 Cool  
         3         20 Mad 

{2}查询

--  JOIN CONDITION:
--  {1} add a comma at the start and at the end of T1.qualities
--  {2} remove all blanks (spaces) from T1.qualities
--  {3} use LIKE and the qid (of allqualities),wrapped in commas
--
--  inline view: use UNIQUE,otherwise we may get counts > 3
--

select person
from (
  select unique person,category
  from table1 T1 
    join allqualities A 
      on ',' || replace( T1.qualities,' ','' ) || ',' like '%,' || A.qid || ',%'
)
group by person
having count(*) = ( select count( distinct category ) from allqualities )
;

-- result
PERSON   
Bob      
Mary 

经过Oracle 18c和11g测试。 DBfiddle here。