日期:2014-05-18  浏览次数:20448 次

【求助】求一语句
SQL code

test_item_name  pointer_condition_text  pointer_condition_text2
a                                               a<1;b<=5  
b 
c        
d                   <998        



想要的结果为
SQL code

test_item_name
a
c
d



解释:取pointer_condition_text不为空,
或者pointer_condition_text为空,但是pointer_condition_text2中不包含text_item_name的数据。


------解决方案--------------------
SQL code

with t1 as
(
     select 'a' c1,null c2,'a<1;b<=5' c3 from dual
     union all
     select 'b' c1,null c2,null c3 from dual
     union all
     select 'c' c1,null c2,null c3 from dual
     union all
     select 'd' c1,'<998' c2,null c3 from dual
)

select distinct c1
from t1,
(
     select replace(regexp_substr(c3,'[^;]+',1,level),';',' ') val
     from t1 
     connect by level<=length(c3)-length(replace(c3,';',''))+1
) t2 
where length(replace(val,c1,'')) < length(val) or c2 is not null
order by c1

     c1
----------------
1    a
2    b
3    d