oracle join on 数据过滤问题
程序员文章站
2023-11-17 10:55:04
复制代码 代码如下:select a.f_username from ( select /*+parallel(gu,4)*/distinct gu.f_username...
复制代码 代码如下:
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 不懂先把数据给过滤掉然后在来连接吗? 太笨了!而且这样把符合条件的数据也过滤掉了
推荐阅读
-
oracle join on 数据过滤问题
-
PDO取Oracle lob大字段,当数据量太大无法取出的问题的解决办法
-
关于Oracle中sys、system和Scott用户下的数据库连接问题
-
oracle数据库用户的密码重置问题
-
Oracle数据库从入门到精通 单行函数问题
-
oracle数据库查询没问题,正常显示两个汉字;但是java后台Hibernate查询的时候只显示第一个汉字怎么解决?
-
oracle数据库锁表问题的解决办法
-
Java oracle数据库填数据时报错ora-12505问题解决办法
-
mybatis oracle proc 数据库测试没问题,java调用就异常 ORA-00900: 无效 SQL 语句
-
Windows 64位下装安装Oracle 11g,PLSQL Developer的配置问题,数据库显示空白的完美解决方案(图文教程)