从自下而上的解析顺序来看.select t1.code,t3.name,A.score from t1,t3,(select * from t2 where number='1' and PID='011') A where t1.number='1' and t1.ID='0001' and t1.code=t3.code(+) and t1.code=A.code(+) 改成 select t1.code,t3.name,A.score from t1,t3,(select * from t2 where number='1' and PID='011') A where t1.code=t3.code(+) and t1.code=A.code(+) and t1.number='1' and t1.ID='0001' and t3.code=a.code
数据量很大么?另外觉得你的语句好像有些不对,select * from t2 where number='1' and PID='011',为什么就取number='1' and PID='011'的呢?你是为了使数据唯一么?那如果对于code=02的也有多条记录的情况呢,code=03也有多条呢?把你的需求说一下好么?我觉得你不应该这样取唯一记录。
sql语句的逻辑有问题为得到如下信息 --code name score --01 A01 10 --02 A02 20 --03 A03只需要用t2关联到t3,而根本与t1无关 这里,姑且认为,是与t1相关的 很明显t3表是master表(代码表) 所以这个关联是右联,即t2 right join t3 同样的,是t1右联到t3那么,sql语句应当这样写select t3.code, t3.name, t2.score from t3 left join t1 on t1.code = t3.code and t1.number='1' and t1.ID='0001' left jion t2 on t2.code = t3.code and t2.number='1' and t2.PID='011' ;
楼上的也许你也没有明白搂主的意思,t1表是有用的,你看最后的数据要求没有04。 另外建议代码不要用标准sql,oracle自已的外联语句是用(+)的,据说这样优化性高一些。select t3.code, t3.name, t2.score from t3,t2,(select distinct code from t1) t4 where t3.code = t2.code(+) and t3.code(+)= t4.code; 试一下吧。
t2的pk:PID,code,number
t3的pk:code
from t1,t3,(select * from t2 where number='1' and PID='011') A
where t1.number='1' and t1.ID='0001'
and t1.code=t3.code(+)
and t1.code=A.code(+)
改成
select t1.code,t3.name,A.score
from t1,t3,(select * from t2 where number='1' and PID='011') A
where t1.code=t3.code(+)
and t1.code=A.code(+)
and t1.number='1' and t1.ID='0001'
and t3.code=a.code
--code name score
--01 A01 10
--02 A02 20
--03 A03只需要用t2关联到t3,而根本与t1无关
这里,姑且认为,是与t1相关的
很明显t3表是master表(代码表)
所以这个关联是右联,即t2 right join t3
同样的,是t1右联到t3那么,sql语句应当这样写select t3.code, t3.name, t2.score
from t3
left join t1 on
t1.code = t3.code
and t1.number='1' and t1.ID='0001'
left jion t2 on
t2.code = t3.code
and t2.number='1' and t2.PID='011'
;
另外建议代码不要用标准sql,oracle自已的外联语句是用(+)的,据说这样优化性高一些。select t3.code, t3.name, t2.score
from t3,t2,(select distinct code from t1) t4
where t3.code = t2.code(+) and t3.code(+)= t4.code;
试一下吧。