求一SQL

[yjdfff]

有三张表（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 CCC

T2：
COL1 COL2
111 FSDF
111 FFE
222 FDFS
222 FDFD
222 DFSF
222 FSSSS

T3：
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

[welyngj]

SELECT T2.COL1,T2.COL2,T3.COL2 FROM (SELECT T2.COL1,T2.COL2 FROM T1,T2 WHERE T1.COL1=T2.COL1) T21
FULL OUTER JOIN   (SELECT T3.COL1,T3.COL2 FROM T1,T3 WHERE T1.COL1=T3.COL1) T31 ON T21.COL1=T31.COL1

[welyngj]

SELECT T21.COL1,T21.COL2,T31.COL2 FROM (SELECT T2.COL1,T2.COL2 FROM T1,T2 WHERE T1.COL1=T2.COL1) T21
FULL OUTER JOIN   (SELECT T3.COL1,T3.COL2 FROM T1,T3 WHERE T1.COL1=T3.COL1) T31 ON T21.COL1=T31.COL1

[yjdfff]

你写的这个不对哟，生成的是一个笛卡尔积了

[yjdfff]

你可以用一点数据测试一下


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;

[welyngj]

SELECT T21.COL1,T21.COL2,T31.COL2 FROM (SELECT T2.COL1,T2.COL2 FROM T1,T2 WHERE T1.COL1=T2.COL1) T21
LEFT OUTER JOIN (SELECT T3.COL1,T3.COL2 FROM T1,T3 WHERE T1.COL1=T3.COL1) T31 ON T21.COL1=T31.COL1

[yjdfff]

1 111 XXX DFE
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应该只有三条

[CTO1206]

确实不对哦！！！

[yjdfff]

也就是以T2，T3表中最多的行数为准，条件是COL1相等

[yjdfff]

高手些帮帮忙撒

[yjdfff]

welyngj

还有其它方法没？

[welyngj]

这个有点复杂。。因为他并不是单纯的连接。

[yjdfff]

不用程序，你还有办法没？

[welyngj]

等待高手。。。

[Run_LeoCat]

关注中