SELECT a1,a2,a3 FROM (select a.a1,a.a2,a.a3,b.b1 from A,B where a.a1=b.b1) tem CONNECT BY PRIOR a3 = a1 START WITH a1 = '001'??
oracle8:SELECT a1,a2,a3 FROM (select a.a1,a.a2,a.a3,b.b1 from a,b) tem CONNECT BY PRIOR a3 = a1 AND a1 =b1 START WITH a1 = '001';
好像不行呀,同样是出不允许两个表做连接的错误,ORA-01437。请问还有其他的办法吗?
oracle8:create table a(a1 varchar2(3),a2 varchar(1),a3 varchar(3)); insert into a values('001','0','002'); insert into a values('001','1','003'); insert into a values('002','0','004'); insert into a values('002','1','004'); insert into a values('003','0','004'); insert into a values('004','0','005'); insert into a values('005','0','009'); insert into a values('006','0','007'); insert into a values('009','0','006');create table b(b1 varchar2(3)); insert into b values('001'); insert into b values('002'); insert into b values('003'); insert into b values('004'); insert into b values('005'); insert into b values('006'); insert into b values('007');SELECT A.a1,A.a2,A.a3 FROM A WHERE A.a1 IN( select B.b1 as a1 from B) CONNECT BY PRIOR A.a3 = A.a1 START WITH A.a1 = '001';这样写行吗?我测的时候通过了。
参考: http://search.csdn.net/expert/topic/61/6101/2003/3/19/1551178.htmSELECT A.a1,A.a2,A.a3 FROM (select a1,a2,a3,b1 from A,B where a1=b1) CONNECT BY a1='001' START WITH PRIOR A.a3 = A.a1
insert into a values('001','0','002');
insert into a values('001','1','003');
insert into a values('002','0','004');
insert into a values('002','1','004');
insert into a values('003','0','004');
insert into a values('004','0','005');
insert into a values('005','0','009');
insert into a values('006','0','007');
insert into a values('009','0','006');create table b(b1 varchar2(3));
insert into b values('001');
insert into b values('002');
insert into b values('003');
insert into b values('004');
insert into b values('005');
insert into b values('006');
insert into b values('007');SELECT A.a1,A.a2,A.a3 FROM A
WHERE A.a1 IN( select B.b1 as a1 from B)
CONNECT BY PRIOR A.a3 = A.a1
START WITH A.a1 = '001';这样写行吗?我测的时候通过了。
http://search.csdn.net/expert/topic/61/6101/2003/3/19/1551178.htmSELECT A.a1,A.a2,A.a3 FROM (select a1,a2,a3,b1 from A,B where a1=b1) CONNECT BY a1='001' START WITH PRIOR A.a3 = A.a1