如果是个变量呢,比如str,str的取值和上面的取值规则一样 select * from A where ... a1的值包含str ...? 这样该怎么写?
比如str='2,3,5,82,80': select * from a where a.a1 in (select REGEXP_SUBSTR('2,3,5,82,80', '[^,]+', 1, LEVEL) STR from dual CONNECT BY LEVEL <= REGEXP_COUNT('2,3,5,82,80', ',') + 1);
如果是包含的话应该为这样:select distinct t1.a1 from a t1, (select REGEXP_SUBSTR('2,3,5,82,80', '[^,]+', 1, LEVEL) str from dual CONNECT BY LEVEL <= REGEXP_COUNT('2,3,5,82,80', ',') + 1) t2 where t1.a1 like '%' || t2.str || '%';
如果是包含的话应该为这样:select distinct t1.a1 from a t1, (select REGEXP_SUBSTR('2,3,5,82,80', '[^,]+', 1, LEVEL) str from dual CONNECT BY LEVEL <= REGEXP_COUNT('2,3,5,82,80', ',') + 1) t2 where t1.a1 like '%' || t2.str || '%';zlloct,谢谢你啦,问题解决了!我再去研究下这个语句。
select * from A
where
...
a1的值包含str
...?
这样该怎么写?
select *
from a
where a.a1 in
(select REGEXP_SUBSTR('2,3,5,82,80', '[^,]+', 1, LEVEL) STR
from dual
CONNECT BY LEVEL <= REGEXP_COUNT('2,3,5,82,80', ',') + 1);
from a t1,
(select REGEXP_SUBSTR('2,3,5,82,80', '[^,]+', 1, LEVEL) str
from dual
CONNECT BY LEVEL <= REGEXP_COUNT('2,3,5,82,80', ',') + 1) t2
where t1.a1 like '%' || t2.str || '%';
from a t1,
(select REGEXP_SUBSTR('2,3,5,82,80', '[^,]+', 1, LEVEL) str
from dual
CONNECT BY LEVEL <= REGEXP_COUNT('2,3,5,82,80', ',') + 1) t2
where t1.a1 like '%' || t2.str || '%';zlloct,谢谢你啦,问题解决了!我再去研究下这个语句。
z_shousi兄,谢谢你的及时回答,让我进一步提出问题才找到完整的答案!