select c_kind_no, n_fee, c_prod_no, prod_no
from (select /*d.c_kind_no*/rpfunction.getKindNo(d.c_kind_no ,prod.c_prod_no,'') c_kind_no,
p.c_prod_no,
prod.c_prod_no prod_no,
prod.c_nme_cn,
d.n_comm + d.n_duty + d.n_spl_comm as n_fee /*,d.**/
from WEB_RI_INTER_cont_BILL_MAIN m,WEB_RI_INTER_cont_BILL_DTL d,
/* left join*/ WEB_RI_INTER_cont_BILL_prod p,
/* on d.c_pk_id = p.c_bill_dtl_pk_id*/
/*left join*/ web_prd_prod prod
/* on p.c_prod_no = prod.c_prod_no*/
where m.c_pk_id = d.c_main_pk_id
and d.c_pk_id = p.c_bill_dtl_pk_id(+)
and p.c_prod_no = prod.c_prod_no(+)
and nvl(m.n_month_rbk_mrk,1) in ('0','1')
and upper(m.c_billprd_type) <> 'S' )
where c_kind_no = '01';
有这样的一个sql,本来预期的查询结果应该只返回c_kind_no='01'的数据,但是实际返回的却有c_kind_no不为'01'的数据,请各位大虾指教SQL
from (select /*d.c_kind_no*/rpfunction.getKindNo(d.c_kind_no ,prod.c_prod_no,'') c_kind_no,
p.c_prod_no,
prod.c_prod_no prod_no,
prod.c_nme_cn,
d.n_comm + d.n_duty + d.n_spl_comm as n_fee /*,d.**/
from WEB_RI_INTER_cont_BILL_MAIN m,WEB_RI_INTER_cont_BILL_DTL d,
/* left join*/ WEB_RI_INTER_cont_BILL_prod p,
/* on d.c_pk_id = p.c_bill_dtl_pk_id*/
/*left join*/ web_prd_prod prod
/* on p.c_prod_no = prod.c_prod_no*/
where m.c_pk_id = d.c_main_pk_id
and d.c_pk_id = p.c_bill_dtl_pk_id(+)
and p.c_prod_no = prod.c_prod_no(+)
and nvl(m.n_month_rbk_mrk,1) in ('0','1')
and upper(m.c_billprd_type) <> 'S' )
where c_kind_no = '01';
有这样的一个sql,本来预期的查询结果应该只返回c_kind_no='01'的数据,但是实际返回的却有c_kind_no不为'01'的数据,请各位大虾指教SQL
解决方案 »
- 一个sql的查询 求教怎么做
- 两表关联更新sql语句怎么写
- sql语句问题
- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$关于ORA-00937 不是单组分组函数 问题,在线等,解决即给分$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
- orecla端口被防火墙封了~~ 怎么办?
- alter table BFJXC.SPXX_JB add constraint uk_spgg_test unique(SPGG)提示:
- oracle出现的怪问题,在服务中启动监听器启动不了??
- [高分求教]如何把一个表中的数据插入到另外一台ORACLE服务器数据库的一个表中。
- 为什么创建触发器老是说,带有编译错误,处于Invalid状态
- 【牛人看一下吧】☆★☆★☆ 菜问题:为什么tablespace里面凭空出来好几十个表???☆★☆★☆
- oracle多表联查问题
- 请各位大侠帮个忙,如何oracle中导出数据到sql server中去
没见过这种写法?
从而导致最外层的条件被推进到内部的视图中,结果实际返回的是:
select /*d.c_kind_no*/rpfunction.getKindNo(d.c_kind_no ,prod.c_prod_no,'') c_kind_no,
而这个值实际是不一定为01。看执行计划就很明白了。
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。C:\Users\issuser>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期五 7月 12 09:41:02 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn circi/circi@BBORA_10.1.4.2;
已连接。
SQL> set autotrace traceonly
SQL> select c_kind_no, n_fee, c_prod_no, prod_no
2 from (select /*d.c_kind_no*/rpfunction.getKindNo(d.c_kind_no ,prod.c_pro
d_no,'') c_kind_no,
3 p.c_prod_no,
4 prod.c_prod_no prod_no,
5 prod.c_nme_cn,
6 d.n_comm + d.n_duty + d.n_spl_comm as n_fee /*,d.**/
7 from WEB_RI_INTER_cont_BILL_MAIN m,WEB_RI_INTER_cont_BILL_DTL d, 8 /* left join*/ WEB_RI_INTER_cont_BILL_prod p,
9 /* on d.c_pk_id = p.c_bill_dtl_pk_id*/
10 /*left join*/ web_prd_prod prod
11 /* on p.c_prod_no = prod.c_prod_no*/
12 where m.c_pk_id = d.c_main_pk_id
13 and d.c_pk_id = p.c_bill_dtl_pk_id(+)
14 and p.c_prod_no = prod.c_prod_no(+)
15 and nvl(m.n_month_rbk_mrk,1) in ('0','1')
16 and upper(m.c_billprd_type) <> 'S' )
17 where c_kind_no = '01'
18 /已选择68行。
执行计划
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Inst |IN-OUT|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1068 | 10 (10)| | |
| 1 | HASH JOIN OUTER | | 6 | 1038 | 10 (10)| | |
| 2 | REMOTE | | 1 | 45 | 3 (0)| HEXIN | R->S |
| 3 | FILTER | | 11 | 352 | 3 (0)| | |
| 4 | REMOTE | | | | | HEXIN | R->S |
------------------------------------------------------------------------------Remote SQL Information (identified by operation id):
---------------------------------------------------- 2 - SELECT "A1"."C_PK_ID","A1"."N_MONTH_RBK_MRK","A1"."C_BILLPRD_TYPE
","A2"."C_KIND_NO","A2"."C_KIND_NO","A2"."N_SPL_COMM","A2"."N_DUTY","A2"
."N_COMM","A2"."C_MAIN_PK_ID","A2"."C_PK_ID" FROM
"WEB_RI_INTER_CONT_BILL_MAIN" "A1","WEB_RI_INTER_CONT_BILL_DTL" "A2"
WHERE "A1"."C_PK_ID"="A2"."C_MAIN_PK_ID" AND
UPPER("A1"."C_BILLPRD_TYPE")<>'S' AND (NVL("A1"."N_MONTH_RBK_MRK",1)=0
OR NVL("A1"."N_MONTH_RBK_MRK",1)=1) (accessing
'HEXIN.REGRESS.RDBMS.DEV.US.ORACLE.COM' ) 4 - SELECT "A1"."C_PROD_NO","A1"."C_BILL_DTL_PK_ID","A1"."C_PROD_NO",
"A2"."C_PROD_NO","A2"."C_PROD_NO","A2"."C_PROD_NO","A2"."C_PROD_NO"
FROM "WEB_RI_INTER_CONT_BILL_PROD" "A1","WEB_PRD_PROD" "A2" WHERE
"A1"."C_PROD_NO"="A2"."C_PROD_NO"(+) (accessing
'HEXIN.REGRESS.RDBMS.DEV.US.ORACLE.COM' )
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
40 recursive calls
1 db block gets
4 consistent gets
0 physical reads
364 redo size
1657 bytes sent via SQL*Net to client
382 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
68 rows processed
执行计划可能看不大出来,因为那几个表是远程数据库的