这样的语句改成表连接,怎么改
select
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from rimbase.cbl_pair cp
where cp.opr_state_id = 170003
and cp.delete_state = 0
and not exists
(
select * from
rimbase.asn_res_link arl
where
arl.res_id = cp.pair_id
and arl.delete_state = 0
)
select
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from rimbase.cbl_pair cp
where cp.opr_state_id = 170003
and cp.delete_state = 0
and not exists
(
select * from
rimbase.asn_res_link arl
where
arl.res_id = cp.pair_id
and arl.delete_state = 0
)
解决方案 »
- ora-00942提示找不到表
- 用PL/SQL developer链接数据库服务端后,如何导出其数据库?
- 不存在的表问什么能查到信息呢?
- Oracle 删除表,同义词,数据库链等等都出现问题 触发器
- 一个返回查询结果的ORACLE存储过程哪里出错了?请教!
- 在virtual box上安装cluster提示错误
- 在oracle的存储过程里能对一个变量case吗?
- oracle PL/SQL 中使用UTL_FILE的问题!(在线等待)
- select 语句中group by 子项中的分析顺序是怎么样的??
- oracle表连接怎么做,连接后可以同步更新数据吗?
- oracle管理员密码忘记啦??怎么办呀?????
- 欢迎试用:OraSQL
我听说优化语句上面说可以不用not exists,改成表链接,速度更快,不会改
另外能不能解释下“表链接”的具体含义?
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from rimbase.cbl_pair cp,rimbase.asn_res_link arl
where
(arl.res_id <> cp.pair_id
or arl.delete_state <> 0 )
and cp.opr_state_id = 170003
and cp.delete_state = 0
FROM RIMBASE.CBL_PAIR CP, RIMBASE.ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
AND ARL.RES_ID IS NULL;
create table cbl_pair
(pair_id char(20),
opr_state_id number,
delete_state number
)
insert into cbl_pair values('01234567890123456789', 170003,0);
insert into cbl_pair values('01234567890123456788', 170003,0);
insert into cbl_pair values('01234567890123456786', 170003,1);
insert into cbl_pair values('01234567890123456787', 170004,0);create table asn_res_link
(res_id char(20),
delete_state number
)
insert into asn_res_link values('01234567890123456789', 1);
insert into asn_res_link values('01234567890123456788', 0);
insert into asn_res_link values('01234567890123456786', 1);
insert into asn_res_link values('01234567890123456787', 0);
select
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from cbl_pair cp
where cp.opr_state_id = 170003
and cp.delete_state = 0
and not exists
(
select * from
asn_res_link arl
where
arl.res_id = cp.pair_id
and arl.delete_state = 0
)
--结果:
'01234567890123456789', '901' , 'S004'
select distinct
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from cbl_pair cp,asn_res_link arl
where
(arl.res_id <> cp.pair_id
or arl.delete_state <> 0 )
and cp.opr_state_id = 170003
and cp.delete_state = 0--结果:
'01234567890123456788', '901' , 'S004'
'01234567890123456789', '901' , 'S004'
SELECT CP.PAIR_ID, SUBSTR(TRIM(CP.PAIR_ID), 10, 3), 'S004'
FROM CBL_PAIR CP,ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
AND ARL.RES_ID IS NULL;--结果:
NULL
SELECT CP.PAIR_ID, SUBSTR(TRIM(CP.PAIR_ID), 10, 3), 'S004'
FROM CBL_PAIR CP,ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
--结果:
'01234567890123456788', '901' , 'S004'结论:5楼、7楼的语句似乎都不是很正确。
SELECT CP.PAIR_ID, SUBSTR(TRIM(CP.PAIR_ID), 10, 3), 'S004'
FROM CBL_PAIR CP,ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE(+) = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
AND ARL.RES_ID IS NULL;