代码如下:
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 不懂先把数据给过滤掉然后在来连接吗？ 太笨了！而且这样把符合条件的数据也过滤掉了