select tt.b, tt.c, t2.g from ( select t1.a, t1.c, t1.d, t1.e, t2.f from t1 left join t2 on (t2.c=t1.c) ) tt left join t3 on (t3.d=tt.d and t2.e=tt.e and t2.f=tt.f) where tt.a=varA;
我自己想到的办法 select 1.b,1.c,tt.g from 1,3,(select * from 1,2 where 1.c=2.c(+)) tt where 1.a='x' and 1.a=tt.a and tt.d=3.d(+) and tt.e=3.e(+) and tt.f=3.f(+)
from T1,T2,T3
where T1.c=T2.c
and T2.f=T3.f(+)
既然T1.c非空,T2.c又是Key值,T2.f非空,這樣寫應該沒有問題吧
t1.c在t2中可能没有对应值
from
(
select T2.c,T3.g
from T2,T3
where T2.f=T3.f(+)
) T4,T1
where T1.c=T4.c(+)
t1.c,
t3.g
from t1
join t3 on (t3.d=t1.d)
where t1.a=varA;
如果T#表中没有对应的数据,可以过滤,也可以显示空值,后者可用LEFT JOIN
分析如下:
1.B和C在T1中有;
2.G需从T3中取;
3.T1与T3可以通过D关联;这一点跟T1与T2通过C关联有区别吗?
那么,请教一下为什么要从T2表绕一下呢?
那么按你的检索,a中所有数据都会出现两条
tt.c,
t2.g
from
(
select t1.a,
t1.c,
t1.d,
t1.e,
t2.f
from t1
left join t2 on (t2.c=t1.c)
) tt
left join t3 on (t3.d=tt.d and t2.e=tt.e and t2.f=tt.f)
where tt.a=varA;
select 1.b,1.c,tt.g
from 1,3,(select * from 1,2 where 1.c=2.c(+)) tt
where
1.a='x'
and 1.a=tt.a
and tt.d=3.d(+)
and tt.e=3.e(+)
and tt.f=3.f(+)