test_item_name pointer_condition_text pointer_condition_text2 a a<1;b<=5 b c d <998 想要的结果为test_item_name a c d 解释:取pointer_condition_text不为空, 或者pointer_condition_text为空,但是pointer_condition_text2中不包含text_item_name的数据。
select * from tb where pointer_condition_text is not null or (pointer_condition_text is null and charindex(pointer_condition_text,pointer_condition_text2)=0)
擦,oracle中的charindex可以用哪个语句实现。 木想到两个不一样。
这种方法不对,结果不对, 我换成oracle 中的instr select * from test_item where pointer_condition_text is not null or (pointer_condition_text is null and instr(pointer_condition_text2,test_item_name)=0) 这样c都查不出来
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
select * from tb
where pointer_condition_text is not null
or (pointer_condition_text is null and charindex(pointer_condition_text,pointer_condition_text2)=0)
木想到两个不一样。
我换成oracle 中的instr
select * from test_item
where pointer_condition_text is not null
or (pointer_condition_text is null and instr(pointer_condition_text2,test_item_name)=0)
这样c都查不出来
或者pointer_condition_text为空,但是pointer_condition_text2中不包含text_item_name的数据。你要搞明白“或者”是什么意思
where charindex(test_item_name,pointer_condition_text2)=0
我就是写这样的语句,也一条记录都木有啊,
囧!这条语句至少会返回c啊
a
c
d
不知道是什么个算法出来的。b
c一样的,为什么只有C没有b
当然没有,两个字段都是null,null代表unknow,unknow在unknow中的位置肯定是unknow,也就是说还是null
null<>0
SQL 中,只查询出了这条语句
取出pointer_condition_text不为空,
或者pointer_condition_text为空并且只要pointer_condition_text2这一列中有包含test_item_name,该test_item_name所在的行就不取出来
或者pointer_condition_text为空并且pointer_condition_text2这列中不包含test_item_name。
例如:b这行对应的pointer_condition_text2为null,但是a对应的pointer_condition_text2中包含b,所以b不在结果集中,
但是c这行,没有任何一个pointer_condition_text2包含它,所以它应该显示出来
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
我直接放代码里实现好了。
写sql真蛋疼