select a.* from a,b where a.name like '%'||b.name||'%'
方法2: --判断a表中的name列是否存在b表中name列的值 select a.* from a,b where instr(a.name,b.name)>0
SQL> with a as( 2 select 1 id,'james,tom,thomas,scott' name from dual union all 3 select 2,'albort,alferd' from dual union all 4 select 3,'alise,smith,jones' from dual union all 5 select 4,'clerk,king' from dual) 6 , b as( 7 select 'allen' name from dual union all 8 select 'clerk' from dual union all 9 select 'smith' from dual union all 10 select 'king' from dual) 11 select a.*,b.* 12 from a,b 13 where a.name like '%'||b.name||'%' 14 /
ID NAME NAME ---------- ---------------------- ----- 3 alise,smith,jones smith 4 clerk,king clerk 4 clerk,king king -- 11 select a.*,b.* 12 from a,b 13 where instr(a.name,b.name)>0 14 /
ID NAME NAME ---------- ---------------------- ----- 3 alise,smith,jones smith 4 clerk,king clerk 4 clerk,king king
from a,b
where a.name like '%'||b.name||'%'
--判断a表中的name列是否存在b表中name列的值
select a.*
from a,b
where instr(a.name,b.name)>0
SQL> with a as(
2 select 1 id,'james,tom,thomas,scott' name from dual union all
3 select 2,'albort,alferd' from dual union all
4 select 3,'alise,smith,jones' from dual union all
5 select 4,'clerk,king' from dual)
6 , b as(
7 select 'allen' name from dual union all
8 select 'clerk' from dual union all
9 select 'smith' from dual union all
10 select 'king' from dual)
11 select a.*,b.*
12 from a,b
13 where a.name like '%'||b.name||'%'
14 /
ID NAME NAME
---------- ---------------------- -----
3 alise,smith,jones smith
4 clerk,king clerk
4 clerk,king king
--
11 select a.*,b.*
12 from a,b
13 where instr(a.name,b.name)>0
14 /
ID NAME NAME
---------- ---------------------- -----
3 alise,smith,jones smith
4 clerk,king clerk
4 clerk,king king