oracle join过滤数据,oracle join on 数据过滤问题

因为在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 > (select trunc(sysdate,'Y')FROM DUAL)

and   gu.f_lotid=1

and   gu.f_playid=4

and   gu.f_paymoney>=1500

) A

left join

(

select

from t_base_vip_customes

and ((vu.f_passeddate is null ) or (vu.f_passeddate >  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 > (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>=rec_viplotPlay.F_Conditon_ValuesA

and ((vu.f_passeddate is null ) or (vu.f_passeddate >  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 >  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 > (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>=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 > = 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)>=rec_VipLotPlay.F_Conditon_ValuesA

) A

left join

(

select f_username from t_base_vip_customes vu

where vu.f_passeddate > 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