|
<div class="codetitle"><a style="CURSOR: pointer" data="55701" class="copybut" id="copybut55701" onclick="doCopy('code55701')"> 代码如下:<div class="codebody" id="code55701">
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
采用下面的语句 只能查出部分用户
<div class="codetitle"><a style="CURSOR: pointer" data="47415" class="copybut" id="copybut47415" onclick="doCopy('code47415')"> 代码如下:<div class="codebody" id="code47415">
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
执行计划：
<div class="codetitle"><a style="CURSOR: pointer" data="25389" class="copybut" id="copybut25389" onclick="doCopy('code25389')"> 代码如下:<div class="codebody" id="code25389">
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
后来改成了下面就能全部查出来了
<div class="codetitle"><a style="CURSOR: pointer" data="64910" class="copybut" id="copybut64910" onclick="doCopy('code64910')"> 代码如下:<div class="codebody" id="code64910">
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 不懂先把数据给过滤掉然后在来连接吗？ 太笨了！而且这样把符合条件的数据也过滤掉了 （编辑：莱芜站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
