1.关于exists和in
in是循环的方式,在内存中处理,
exists是执行数据库查询,
?
select tpd.personaccountid,sum(nvl(tpd.CREDIT_SUM,0)) as bjsr,sum(nvl(tpd.INTEREST_INCOME,sum(nvl(tpd.DEBIT_SUM,0)) as bjzc from TGP_PERSON_DETAIL tpd left join TAP_FUNDBUSINESS tfb on tpd.FUNDBUSINESS_ID = tfb.FUNDBUSINESS_ID where tpd.PERSONACCOUNTID in ( select personaccountid from TGP_PERSONACCOUNT pa left join tgp_person person on pa.personid = person.personid where person.zjhm = ‘330903196209160251‘ ) group by tpd.personaccountid
执行100次 耗时0.017S左右
?
select tpd.personaccountid,0)) as bjzc from TGP_PERSON_DETAIL tpd left join TAP_FUNDBUSINESS tfb on tpd.FUNDBUSINESS_ID = tfb.FUNDBUSINESS_ID where exists ( select personaccountid from (select personaccountid from TGP_PERSONACCOUNT pa left join tgp_person person on pa.personid = person.personid where person.zjhm = ‘330903196209160251‘) a where tpd.personaccountid = a.personaccountid ) group by tpd.personaccountid
执行100次 耗时0.020S左右
以上都为SELECT FORM a IN b(OR EXISTS B)? B中数据少的时候 采用IN 多的时候采用EXISTS??
?
select tpd.personaccountid,0)) as bjzc from TGP_PERSON_DETAIL tpd left join TAP_FUNDBUSINESS tfb on tpd.FUNDBUSINESS_ID = tfb.FUNDBUSINESS_ID HAVING exists ( select personaccountid from (select personaccountid from TGP_PERSONACCOUNT pa left join tgp_person person on pa.personid = person.personid where person.zjhm = ‘330903196209160251‘) a where tpd.personaccountid = a.personaccountid ) group by tpd.personaccountid
执行100次 耗时2.36S左右
因此先筛选数据再分组 效率更高
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|