---
title: "oracle join on 数据过滤问题"
author: "winzenghua"
date: "2009-07-13T17:13:00Z"
tags: []
slug: "oracle-join-on"
draft: false
lastmod: "2009-07-13T17:13:00Z"
id: 987867
url: "https://java4gis.iteye.com/blog/987867"
---

因为在FOR .. IN () LOOP 游标中使用 所以不能采用下面的查询语句做游标

select a.f\_username  
from  
(  
SELECT /\*+parallel(gu,4)\*/distinct gu.f\_username  
FROM t\_base\_succprouser gu  
where gu.f\_expectenddate &gt; (select trunc(sysdate,'Y')FROM DUAL)  
and gu.f\_lotid=1  
and gu.f\_playid=4  
and gu.f\_paymoney&gt;=1500  
) A  
left join  
(  
select  
from t\_base\_vip\_customes  
and ((vu.f\_passeddate is null ) or (vu.f\_passeddate &gt; trunc(sysdate,'Y') ))  
and ((vu.f\_lotid is null ) or (vu.f\_lotid=1))  
and ((vu.f\_playid is null ) or (vu.f\_playid=4))  
and ((vu.f\_condtionid is null ) or (vu.f\_condtionid=3))  
)B  
on A.f\_username=B.f\_usernam  
where b.f\_username is null

采用下面的语句 只能查出部分用户

SELECT /\*+parallel(gu,4)\*/distinct gu.f\_username

FROM t\_base\_succprouser gu  
left join t\_base\_vip\_customes VU on gu.f\_username=vu.f\_username  
gu.f\_expectenddate &gt; (select trunc(sysdate,'Y')FROM DUAL)  
and gu.f\_lotid=rec\_viplotplay.f\_lotid  
and gu.f\_playid=rec\_viplotPlay.f\_Playid  
and gu.f\_paymoney&gt;=rec\_viplotPlay.F\_Conditon\_ValuesA

and ((vu.f\_passeddate is null ) or (vu.f\_passeddate &gt; trunc(sysdate,'Y') ))  
and ((vu.f\_lotid is null ) or (vu.f\_lotid=rec\_viplotplay.f\_lotid))

and ((vu.f\_playid is null ) or (vu.f\_playid=rec\_viplotPlay.f\_Playid))  
and ((vu.f\_condtionid is null ) or (vu.f\_condtionid=rec\_viplotPlay.F\_CondtionID))

and vu.f\_username is null

执行计划：

SELECT STATEMENT, GOAL = ALL\_ROWS  
HASH UNIQUE  
NESTED LOOPS OUTER  
PARTITION RANGE ALL  
TABLE ACCESS FULL Object name=T\_BASE\_SUCCPROUSER  
VIEW  
FILTER  
TABLE ACCESS FULL Object name=T\_BASE\_VIP\_CUSTOMES  
FAST DUAL

后来改成了下面就能全部查出来了

SELECT /\*+parallel(gu,4)\*/distinct gu.f\_username

FROM t\_base\_succprouser gu  
left join t\_base\_vip\_customes VU on gu.f\_username=vu.f\_username  
and ((vu.f\_passeddate is null ) or (vu.f\_passeddate &gt; trunc(sysdate,'Y') ))  
and ((vu.f\_lotid is null ) or (vu.f\_lotid=rec\_viplotplay.f\_lotid))

and ((vu.f\_playid is null ) or (vu.f\_playid=rec\_viplotPlay.f\_Playid))  
and ((vu.f\_condtionid is null ) or (vu.f\_condtionid=rec\_viplotPlay.F\_CondtionID))

where gu.f\_expectenddate &gt; (select trunc(sysdate,'Y')FROM DUAL)  
and gu.f\_lotid=rec\_viplotplay.f\_lotid  
and gu.f\_playid=rec\_viplotPlay.f\_Playid  
and gu.f\_paymoney&gt;=rec\_viplotPlay.F\_Conditon\_ValuesA  
and vu.f\_username is null

执行计划：

SELECT STATEMENT, GOAL = ALL\_ROWS  
HASH UNIQUE  
FILTER  
NESTED LOOPS OUTER  
TABLE ACCESS BY GLOBAL INDEX ROWID Object name=T\_BASE\_SUCCPROUSER  
INDEX RANGE SCAN Object name=IX\_BASE\_PROUSER\_LOWEX  
FAST DUAL  
VIEW  
TABLE ACCESS FULL Object name=T\_BASE\_VIP\_CUSTOMES

oracle 不懂先把数据给过滤掉然后在来连接吗？ 太笨了！而且这样把符合条件的数据也过滤掉了

后来发现 游标还是可以用子查询

for rec\_ActiveUser in (  
select a.f\_username  
from  
(  
select f\_username  
from t\_base\_succprouser  
where f\_expectenddate &gt; = trunc(sysdate,'Y')  
and f\_lotid=rec\_viplotplay.f\_lotid  
and f\_playid=rec\_viplotPlay.f\_Playid  
group by f\_username  
having sum(f\_paymoney)&gt;=rec\_VipLotPlay.F\_Conditon\_ValuesA  
) A  
left join  
(  
select f\_username from t\_base\_vip\_customes vu  
where vu.f\_passeddate &gt; trunc(sysdate,'Y')  
and vu.f\_lotid=rec\_viplotplay.f\_lotid  
and vu.f\_playid=rec\_viplotPlay.f\_Playid  
and vu.f\_condtionid=rec\_viplotPlay.F\_CondtionID  
) B on a.f\_username=b.f\_username  
where b.f\_username is null  
)  
loop