select col_1,col_2 from( select col_1, (case when isnumber(col_2) = 1 then to_number(col_2) else 1 end) as col_2 from t_1 ) where col_2>100 试看看,我也没机会试!
使用一个exist查询就可以了 select b.col1,b.col2 from ( select col1,col2 from t_a a where exist (select 1 from t_a b where isnumber(col_2) = 1) ) b where to_number(b.col2)>100;
修改一下:select c.col1,c.col2 from ( select a.col1,a.col2 from t_a a where exist (select 1 from t_a b where isnumber(b.col_2) = 1 and a.col2=b.col2) ) c where to_number(c.col2)>100;
对第一个语句换个顺序不知道行不行: 1.select col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2);
少了点: 1.select col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1;
试试 select /*+ rule */ col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1;
sbaz(万神渡劫):是可以的^_^ --------------------------- select col_1,col_2 from( select col_1, (case when isnumber(col_2) = 1 then to_number(col_2) else 1 end) as col_2 from t_1 ) where col_2>100 ------------------------ 同理:decode函数也可以实现hevin(没有什么是不可能的): 可惜…… bobfang(匆匆过客): 可惜…… ------------------------ select col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1; select /*+ rule */ col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1; select /*+ choose*/ col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1; select /*+ firstrow*/ col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1; select /*+ allrow*/ col_1 from T_1 where to_number(col_2) > 100 and isnumber(col_2) = 1; select /*+ INDEX(T_1)*/ col_1 from T_1 --似乎要和索引先联系了,我是主键col_1无其他索引) where to_number(col_2) > 100 and isnumber(col_2) = 1; -----------以上初步测试行不通-------------
zealot_zk(风中追风) : 很复杂,不同索引和where中包含的关键字不同时,有时行有时不行 ----------------------------- select c.col1,c.col2 from ( select a.col1,a.col2 from t_a a where exist (select 1 from t_a b where isnumber(b.col_2) = 1 and a.col2=b.col2) ) c where to_number(c.col2)>100; ----------实际上就是---------- select * from t_1 where to_number(c.col2)>100 and exist (select '1' from t_a b where isnumber(b.col_2) = 1 and a.rowid=b.rowid) ------------------------------ 这样的句子在我的真实表中(有两个数据库,表结构相同,索引略有不同) 一个能用,一个不能用 巨寒呀 可能索引或其他数据库参数影响了该句SQL的解释顺序 最后导致有时能用有时不能用 ----------------------------- 以上为个人体验,如有不同意见或更加准确的解释, 请高手们不吝赐教……
我觉得我的方法是复杂了些,但应该是可以的,如果按照你的方式进行优化,那where字句中条件的顺序应该调整一下,where exist (select '1' from t_a b where isnumber(b.col_2) = 1 and a.rowid=b.rowid) and to_number(c.col2)>100
写sql出来看看,
能实现的话,应该也是个很简单的sql呀
from(
select col_1,
(case when isnumber(col_2) = 1 then to_number(col_2) else 1 end) as col_2
from t_1 )
where col_2>100
试看看,我也没机会试!
select b.col1,b.col2
from (
select col1,col2 from t_a a
where exist (select 1 from t_a b where isnumber(col_2) = 1)
) b
where to_number(b.col2)>100;
from (
select a.col1,a.col2 from t_a a
where exist (select 1 from t_a b where isnumber(b.col_2) = 1 and a.col2=b.col2)
) c
where to_number(c.col2)>100;
1.select col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2);
1.select col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
select /*+ rule */ col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
---------------------------
select col_1,col_2
from(
select col_1,
(case when isnumber(col_2) = 1 then to_number(col_2) else 1 end) as col_2
from t_1 )
where col_2>100
------------------------
同理:decode函数也可以实现hevin(没有什么是不可能的): 可惜……
bobfang(匆匆过客): 可惜……
------------------------
select col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
select /*+ rule */ col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
select /*+ choose*/ col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
select /*+ firstrow*/ col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
select /*+ allrow*/ col_1 from T_1
where to_number(col_2) > 100 and isnumber(col_2) = 1;
select /*+ INDEX(T_1)*/ col_1 from T_1 --似乎要和索引先联系了,我是主键col_1无其他索引)
where to_number(col_2) > 100 and isnumber(col_2) = 1;
-----------以上初步测试行不通-------------
-----------------------------
select c.col1,c.col2
from (
select a.col1,a.col2 from t_a a
where exist (select 1 from t_a b where isnumber(b.col_2) = 1 and a.col2=b.col2)
) c
where to_number(c.col2)>100;
----------实际上就是----------
select *
from t_1
where to_number(c.col2)>100 and
exist (select '1' from t_a b where isnumber(b.col_2) = 1 and a.rowid=b.rowid)
------------------------------
这样的句子在我的真实表中(有两个数据库,表结构相同,索引略有不同)
一个能用,一个不能用
巨寒呀
可能索引或其他数据库参数影响了该句SQL的解释顺序
最后导致有时能用有时不能用
-----------------------------
以上为个人体验,如有不同意见或更加准确的解释,
请高手们不吝赐教……
and to_number(c.col2)>100