比如表A,B,C,D,E 表A结构如下 id X1 表B结构如下 id X2 表C结构如下 id X3 表D结构如下 id X4 表E结构如下 id X5 要求最后的格式为 id X1 X2 X3 X4 X5 麻烦在于表A中的id项目包含表B,c,d,e 但表B,c,d,e中的id各不相同。 也就是说表A id项目包含表b,c,d,e 如果只有一个表到是很好办,使用左(右)外连接就可以解决问题。 但对多个表的情况没遇见过 望高人赐教!
举个例子 表a id x1 1 01 2 11 3 20 表b id x2 2 30 3 50 表c id x3 1 40 3 60 要求结果: id X1 X2 X3 1 01 40 2 11 30 3 20 50 60
就使用直接left join就行了呀,不过多表left join在数据量比较大的情况下会比较慢 SQL> SQL> create table taba 2 ( 3 id number(16), 4 x1 number(16) 5 );Table createdExecuted in 0.157 secondsSQL> SQL> create table tabb 2 ( 3 id number(16), 4 x2 number(16) 5 );Table createdExecuted in 0.031 secondsSQL> create table tabc 2 ( 3 id number(16), 4 x3 number(16) 5 );Table createdExecuted in 0.016 secondsSQL> select * from taba; ID X1 ----------------- -----------------Executed in 0 secondsSQL> select * from tabb; ID X2 ----------------- -----------------Executed in 0 secondsSQL> select * from tabc; ID X3 ----------------- -----------------Executed in 0 secondsSQL> insert into taba values(1,1);1 row insertedExecuted in 0.016 secondsSQL> insert into taba values(2,11);1 row insertedExecuted in 0 secondsSQL> insert into taba values(3,20);1 row insertedExecuted in 0 secondsSQL> insert into tabb values(2,30);1 row insertedExecuted in 0 secondsSQL> insert into tabb values(3,50);1 row insertedExecuted in 0 secondsSQL> insert into tabc values(1,40);1 row insertedExecuted in 0 secondsSQL> insert into tabc values(3,60);1 row insertedExecuted in 0 secondsSQL> select * from taba; ID X1 ----------------- ----------------- 1 1 2 11 3 20Executed in 0.047 secondsSQL> select * from tabb; ID X2 ----------------- ----------------- 2 30 3 50Executed in 0.015 secondsSQL> select * from tabc; ID X3 ----------------- ----------------- 1 40 3 60Executed in 0.015 secondsSQL> SQL> SELECT taba.ID, taba.x1, tabb.x2, tabc.x3 2 FROM taba LEFT JOIN tabb ON taba.ID = tabb.ID LEFT JOIN tabc ON taba.ID = 3 tabc.ID; ID X1 X2 X3 ----------------- ----------------- ----------------- ----------------- 1 1 40 3 20 50 60 2 11 30 Executed in 0.093 secondsSQL> SQL> SELECT taba.ID, taba.x1, tabb.x2, tabc.x3 2 FROM taba LEFT JOIN tabb ON taba.ID = tabb.ID LEFT JOIN tabc ON taba.ID = 3 tabc.ID 4 ORDER BY taba.ID; ID X1 X2 X3 ----------------- ----------------- ----------------- ----------------- 1 1 40 2 11 30 3 20 50 60Executed in 0.015 secondsSQL>
表a
id x1
1 01
2 11
3 20
表b
id x2
2 30
3 50
表c
id x3
1 40
3 60
要求结果:
id X1 X2 X3
1 01 40
2 11 30
3 20 50 60
SQL>
SQL> create table taba
2 (
3 id number(16),
4 x1 number(16)
5 );Table createdExecuted in 0.157 secondsSQL>
SQL> create table tabb
2 (
3 id number(16),
4 x2 number(16)
5 );Table createdExecuted in 0.031 secondsSQL> create table tabc
2 (
3 id number(16),
4 x3 number(16)
5 );Table createdExecuted in 0.016 secondsSQL> select * from taba; ID X1
----------------- -----------------Executed in 0 secondsSQL> select * from tabb; ID X2
----------------- -----------------Executed in 0 secondsSQL> select * from tabc; ID X3
----------------- -----------------Executed in 0 secondsSQL> insert into taba values(1,1);1 row insertedExecuted in 0.016 secondsSQL> insert into taba values(2,11);1 row insertedExecuted in 0 secondsSQL> insert into taba values(3,20);1 row insertedExecuted in 0 secondsSQL> insert into tabb values(2,30);1 row insertedExecuted in 0 secondsSQL> insert into tabb values(3,50);1 row insertedExecuted in 0 secondsSQL> insert into tabc values(1,40);1 row insertedExecuted in 0 secondsSQL> insert into tabc values(3,60);1 row insertedExecuted in 0 secondsSQL> select * from taba; ID X1
----------------- -----------------
1 1
2 11
3 20Executed in 0.047 secondsSQL> select * from tabb; ID X2
----------------- -----------------
2 30
3 50Executed in 0.015 secondsSQL> select * from tabc; ID X3
----------------- -----------------
1 40
3 60Executed in 0.015 secondsSQL>
SQL> SELECT taba.ID, taba.x1, tabb.x2, tabc.x3
2 FROM taba LEFT JOIN tabb ON taba.ID = tabb.ID LEFT JOIN tabc ON taba.ID =
3 tabc.ID; ID X1 X2 X3
----------------- ----------------- ----------------- -----------------
1 1 40
3 20 50 60
2 11 30 Executed in 0.093 secondsSQL>
SQL> SELECT taba.ID, taba.x1, tabb.x2, tabc.x3
2 FROM taba LEFT JOIN tabb ON taba.ID = tabb.ID LEFT JOIN tabc ON taba.ID =
3 tabc.ID
4 ORDER BY taba.ID; ID X1 X2 X3
----------------- ----------------- ----------------- -----------------
1 1 40
2 11 30
3 20 50 60Executed in 0.015 secondsSQL>
偶得怎么报了错的