Select t1.*,t2.b1,t2.b2,t3.c1,t3.c2 From t1,t2,t3 Where t1.a1=t2.a1(+) And t2.b1=t3.b1(+) Order By t1.a1;
Select t1.*,t2.b1,t2.b2,t3.c1,t3.c2 From t1,t2,t3 Where t1.a1=t2.a1(+) And t2.b1=t3.b1(+) Order By t1.a1;和上面的一样
select a1,a2,a3,b1,b2,c1,c2 from T1,T2,T3 where T1.a1=T2.a1 and T2.b1=T3.b1 order by T1.a1试下这样可以吗? 在线等
先以t2为基准跟t3外连接,结果集是x 再以t1为基准跟x外连接 试试看看:)
select a1,a2,a3,T.* from T1,( select b1,b2,c1,c2 from T2,T3 where T2.b1=T3.b1(+) order by T2.b1 ) T where T1.a1=T.a1(+) order by T1.a1试试看看:)
select a.a1, a.a2, a.a3, b.b1, b.b2, b.c1, b.c2 from T1 a, (select T2.a1, T2.b1, T2.b2, T3.c1, T3.c2 from T2, T3 where T2.b1 = T3.b1(+)) b where a.a1 = b.a1(+)
Select t1.*,t2.b1,t2.b2,t3.c1,t3.c2 From t1,t2,t3 Where t1.a1 = t2.a1(+) And t2.b1 = t3.b1(+) Order By t1.a1 1 1 ds dsf ss dd 2 2 sd sdd 3 3 12 121 fd fdk sss kk 4 a 11 111 我特意测试过了!逻辑也比较简单就是基本的外关联
试了N次了,两个表外关联没问题,三个表时就有问题 T1 130条记录 T2 90几条 T3 不足十条可结果为850多条记录
是不是你的sql里面还有别的东西,我赞成楼上的观点,不会有问题
t1中a1有重复值,或者t2中a1有重复值,或者t2中b1有重复值,或者t3中b1有重复值!
Select t1.*,t2.b1,t2.b2,t3.c1,t3.c2 From t1,t2,t3 Where t1.a1=t2.a1(+) And t2.b1=t3.b1(+) Order By t1.a1结果为: A1 A2 A3 B1 B2 C1 C2 1 1 ll lll b lll 123 123 2 2 l2 l12 a l12 12 123 3 3 lll lll3 c 1 1 2 4 4 aaaa aaaa1 d c 5 5 b5 b6 e w 6 6 c c1 7 7 d d1 8 8 e e1 9 9 f f1 正确 如果,t1.a1中重复,如3个2,相应结果增加了两条,如下: A1 A2 A3 B1 B2 C1 C2 1 1 ll lll b lll 123 123 2 2 l2 l12 a l12 12 123 3 2 1 a a l12 12 123 4 2 2 b a l12 12 123 5 3 lll lll3 c 1 1 2 6 4 aaaa aaaa1 d c 7 5 b5 b6 e w 8 6 c c1 9 7 d d1 10 8 e e1 11 9 f f1 语句应该是正确的,可能是存在重复的数据.
From t1,t2,t3
Where t1.a1=t2.a1(+) And t2.b1=t3.b1(+)
Order By t1.a1;
From t1,t2,t3
Where t1.a1=t2.a1(+) And t2.b1=t3.b1(+)
Order By t1.a1;和上面的一样
from T1,T2,T3
where T1.a1=T2.a1 and T2.b1=T3.b1
order by T1.a1试下这样可以吗? 在线等
再以t1为基准跟x外连接
试试看看:)
from T1,( select b1,b2,c1,c2 from T2,T3 where T2.b1=T3.b1(+) order by T2.b1 ) T
where T1.a1=T.a1(+) order by T1.a1试试看看:)
a.a1,
a.a2,
a.a3,
b.b1,
b.b2,
b.c1,
b.c2
from
T1 a,
(select
T2.a1,
T2.b1,
T2.b2,
T3.c1,
T3.c2
from
T2,
T3
where
T2.b1 = T3.b1(+)) b
where
a.a1 = b.a1(+)
Select t1.*,t2.b1,t2.b2,t3.c1,t3.c2
From t1,t2,t3
Where t1.a1 = t2.a1(+) And t2.b1 = t3.b1(+)
Order By t1.a1 1 1 ds dsf ss dd
2 2 sd sdd
3 3 12 121 fd fdk sss kk
4 a 11 111 我特意测试过了!逻辑也比较简单就是基本的外关联
T1 130条记录
T2 90几条
T3 不足十条可结果为850多条记录
From t1,t2,t3
Where t1.a1=t2.a1(+) And t2.b1=t3.b1(+)
Order By t1.a1结果为:
A1 A2 A3 B1 B2 C1 C2
1 1 ll lll b lll 123 123
2 2 l2 l12 a l12 12 123
3 3 lll lll3 c 1 1 2
4 4 aaaa aaaa1 d c
5 5 b5 b6 e w
6 6 c c1
7 7 d d1
8 8 e e1
9 9 f f1
正确
如果,t1.a1中重复,如3个2,相应结果增加了两条,如下:
A1 A2 A3 B1 B2 C1 C2
1 1 ll lll b lll 123 123
2 2 l2 l12 a l12 12 123
3 2 1 a a l12 12 123
4 2 2 b a l12 12 123
5 3 lll lll3 c 1 1 2
6 4 aaaa aaaa1 d c
7 5 b5 b6 e w
8 6 c c1
9 7 d d1
10 8 e e1
11 9 f f1 语句应该是正确的,可能是存在重复的数据.