有以下三张表,为 A,B,CA表字段为1,2,3
A 1 2 3
数据1 a1 b1 c1
数据2 a2 b2 c2 B表字段为1,4,5
B 1 4 5
数据1 a1 d1 e1 C表字段为4,6,7
C 4 6 7
数据1 d1 f1 g1要求:a和b左联接,b和c全连接,取出a表的1和c表的6。(用一条sql语句)
A 1 2 3
数据1 a1 b1 c1
数据2 a2 b2 c2 B表字段为1,4,5
B 1 4 5
数据1 a1 d1 e1 C表字段为4,6,7
C 4 6 7
数据1 d1 f1 g1要求:a和b左联接,b和c全连接,取出a表的1和c表的6。(用一条sql语句)
FROM a,b,c
WHERE a.1=b.1(+)
AND b.4=c.4
SQL> create table a("1" varchar2(10),"2" varchar2(10),"3" varchar2(10));表已创建。SQL> create table b("1" varchar2(10),"4" varchar2(10), "5" varchar2(10));表已创建。SQL> create table c("4" varchar2(10),"6" varchar2(10), "7" varchar2(10));表已创建。SQL> insert into a values('a1','b1','c1');已创建 1 行。SQL> insert into a values('a2','b2','c2');已创建 1 行。SQL> insert into b values('a1','d1','e1');已创建 1 行。SQL> insert into c values('d1','f1','g1');已创建 1 行。SQL> select m.a,l.g from
2 (select 1 as a, 2 as b ,3 as c from a)m ,
3 (select 1 as a, 4 as e ,5 as f from b)n ,
4 (select 4 as e, 6 as g ,7 as h from c)l
5 where m.a(+)=n.a and n.e=l.e; A G
---------- ----------
1 6
1 6