我想查询A表里的记录,如我要查询A表里的当前时间前三月name是tom的记录,但是这个记录不存在于字段job like'经理'里面
如:
select * from (select t.iFollowid from ent_plan t
where t.dttrackingdate between to_date('20080901','yyyy-MM-dd') and to_date('20080930','yyyy-MM-dd') and
t.chfollowresult like '%3%' ) y where y.iFollowid not in
(select t.iFollowid from ent_plant where t.chfollow like '%4%' or t.chFollow like '%5%'
or t.chFollow like '%6%') 这条语句相当慢花了24秒,大哥用什么解决呢?
如:
select * from (select t.iFollowid from ent_plan t
where t.dttrackingdate between to_date('20080901','yyyy-MM-dd') and to_date('20080930','yyyy-MM-dd') and
t.chfollowresult like '%3%' ) y where y.iFollowid not in
(select t.iFollowid from ent_plant where t.chfollow like '%4%' or t.chFollow like '%5%'
or t.chFollow like '%6%') 这条语句相当慢花了24秒,大哥用什么解决呢?
当然慢了~~
from (select t.iFollowid
from ent_plan t
where t.dttrackingdate between to_date('20080901', 'yyyy-MM-dd') and
to_date('20080930', 'yyyy-MM-dd')
and t.chfollowresult like '%3%') y
where not exists (select 1
from ent_plant
where iFollowid = t.iFollowid
and (t.chfollow like '%4%' or t.chFollow like '%5%' or
t.chFollow like '%6%'));
估计可以提高速度2) 如果编写函数判断是否为经理IsManager(chFollow) 并建立函数索引,也能大大提高速度
SELECT *
FROM (SELECT T.IFOLLOWID
FROM ENT_PLAN T
WHERE T.DTTRACKINGDATE BETWEEN TO_DATE('20080901', 'YYYY-MM-DD') AND
TO_DATE('20080930', 'YYYY-MM-DD')
AND INSTR(T.CHFOLLOWRESULT, '3') > 0) Y
WHERE Y.IFOLLOWID NOT IN
(SELECT T.IFOLLOWID
FROM ENT_PLANT
WHERE LENGTHB(T.CHFOLLOW) <>
LENGTHB(REPLACE(TRANSLATE(T.CHFOLLOW, '456', ' '), ' ', '')));