有三张表(t1,t2,t3),结构一样.要得出T1.COL1=T2.COL1 T1.COL1=T3.COL1 然后以T2,T3表中行数多的显出来。没有对应为空 ,如:T1。COL=‘111’的在T2表中有两行,在T3表中有三行,则显示3行
T1:
COL1 COL2
111 AAA
222 BBB
333 CCCT2:
COL1 COL2
111 FSDF
111 FFE
222 FDFS
222 FDFD
222 DFSF
222 FSSSST3:
COL1 COL2
111 FEFEE
111 FDFE
111 DFEFE
222 FEFE现在要得出的结果如下:
T1.COL1 T2.COL2 T3.COL2
111 FSDF FEFEE
111 FFE FDFE
111 DFEFE
222 FDFS FDDDFS
222 FDFD
222 DFSF
222 FSSSS
T1:
COL1 COL2
111 AAA
222 BBB
333 CCCT2:
COL1 COL2
111 FSDF
111 FFE
222 FDFS
222 FDFD
222 DFSF
222 FSSSST3:
COL1 COL2
111 FEFEE
111 FDFE
111 DFEFE
222 FEFE现在要得出的结果如下:
T1.COL1 T2.COL2 T3.COL2
111 FSDF FEFEE
111 FFE FDFE
111 DFEFE
222 FDFS FDDDFS
222 FDFD
222 DFSF
222 FSSSS
FULL OUTER JOIN (SELECT T3.COL1,T3.COL2 FROM T1,T3 WHERE T1.COL1=T3.COL1) T31 ON T21.COL1=T31.COL1
FULL OUTER JOIN (SELECT T3.COL1,T3.COL2 FROM T1,T3 WHERE T1.COL1=T3.COL1) T31 ON T21.COL1=T31.COL1
CREATE TABLE T1(COL1 VARCHAR2(10),COL2 VARCHAR2(10));
CREATE TABLE T2(COL1 VARCHAR2(10),COL2 VARCHAR2(10));
CREATE TABLE T3(COL1 VARCHAR2(10),COL2 VARCHAR2(10));
insert into T1 (COL1, COL2)
values ('111', 'ABC');
insert into T1 (COL1, COL2)
values ('222', 'BCD');
insert into T1 (COL1, COL2)
values ('333', 'DDD');
insert into T1 (COL1, COL2)
values ('444', 'EEF');
insert into T1 (COL1, COL2)
values ('555', 'SFE');
commit;
insert into T2 (COL1, COL2)
values ('111', 'XXXX');
insert into T2 (COL1, COL2)
values ('111', 'XXX');
insert into T2 (COL1, COL2)
values ('222', 'DDD');
insert into T2 (COL1, COL2)
values ('444', 'EEE');
commit;
insert into T3 (COL1, COL2)
values ('111', 'DFE');
insert into T3 (COL1, COL2)
values ('111', 'DFED');
insert into T3 (COL1, COL2)
values ('111', 'EFS');
insert into T3 (COL1, COL2)
values ('222', 'DFFED');
insert into T3 (COL1, COL2)
values ('222', 'DFEF');
insert into T3 (COL1, COL2)
values ('222', 'FSDFDF');
insert into T3 (COL1, COL2)
values ('444', 'DDD');
insert into T3 (COL1, COL2)
values ('444', 'DDDD');
commit;
LEFT OUTER JOIN (SELECT T3.COL1,T3.COL2 FROM T1,T3 WHERE T1.COL1=T3.COL1) T31 ON T21.COL1=T31.COL1
2 111 XXXX DFE
3 111 XXX DFED
4 111 XXXX DFED
5 111 XXX EFS
6 111 XXXX EFS
7 222 DDD DFFED
8 222 DDD DFEF
9 222 DDD FSDFDF
10 444 EEE DDD
11 444 EEE DDDD
111应该只有三条