有这样一个Oracle数据库问题
表一 有列 t1 t2 t3 t4 t5 t6 t7 t8 其中t1 t2 t3是主键表二 有列 t1 t2 t3 x1 x2 x3 x4 x5 其中t1 t2 t3是主键如何将两个表中主键值不同的所有数据整合到另外的表或视图中？
我的笨方法：
1，创建新表:create table t3 as select * from t1 where t1.t1='qqq';2,插入t1表中与t2表中不匹配的数据insert into t3 (select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null);3,插入t2表中与t1表中不匹配的数据insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);
是不是很笨重呢，测试过程见如下：
连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options--创建示例表及示例数据SQL> create table t1 (t1 varchar(1),t2 varchar(5),t3 varchar(10));表已创建。SQL> insert into t1 values ('1','a','1a');已创建 1 行。SQL> insert into t1 values ('2','b','2b');已创建 1 行。SQL> insert into t1 values ('3','c','3c');已创建 1 行。SQL> create table t2 as select * from t1;表已创建。SQL> insert into t1 values ('9','x','9x');已创建 1 行。SQL> insert into t2 values ('8','Y','8Y');已创建 1 行。--查看示例表SQL> select * from t1;T T2 T3- ----- ----------1 a 1a2 b 2b3 c 3c9 x 9xSQL> select * from t2;T T2 T3- ----- ----------1 a 1a2 b 2b3 c 3c8 Y 8Y--两表中各有一行与另表中不匹配的记录。SQL> select * from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+);T T2 T3 T T2 T3- ----- ---------- - ----- ----------1 a 1a 1 a 1a2 b 2b 2 b 2b3 c 3c 3 c 3c9 x 9xSQL> select * from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3;T T2 T3 T T2 T3- ----- ---------- - ----- ----------1 a 1a 1 a 1a2 b 2b 2 b 2b3 c 3c 3 c 3c8 Y 8YSQL> select * from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null;T T2 T3 T T2 T3- ----- ---------- - ----- ----------9 x 9xSQL> select * from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3 and t1.t1 is null;T T2 T3 T T2 T3- ----- ---------- - ----- ----------8 Y 8Y--生成不匹配表，并插入t1表中与t2表中不匹配的数据SQL> create table t3 as select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null;表已创建。SQL> select * from t3;T T2 T3- ----- ----------9 x 9x--再插入t2表中与t1表中不匹配的记录SQL> insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);已创建 1 行。--查看表3，OK。SQL> select * from t3;T T2 T3- ----- ----------9 x 9x8 Y 8Y你也可以先创建t3表，然后再两次插入两表中不匹配的记录，如下：SQL> drop table t3;表已丢弃。SQL> create table t3 as select * from t1 where t1.t1='qqq';表已创建。--分别插入两中表中不匹配的数据--insert into t3 (select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null);--insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);SQL> insert into t3 (select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null);已创建 1 行。SQL> insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);已创建 1 行。SQL> select * from t3;T T2 T3- ----- ----------9 x 9x8 Y 8YSQL>--OK
但，另有高人一个语句就查出来了，不用这么麻烦了!
SELECTt1.t1 AS t1_t1,t1.t2 AS t1_t2,t1.t3 AS t1_t3,t2.t1 AS t2_t1,t2.t2 AS t2_t2,t2.t3 AS t2_t3FROMt1 FULL JOIN t2 ON( t1.t1 = t2.t1AND t1.t2 = t2.t2AND t1.t3 = t2.t3)WHEREt1.t1 IS NULLOR t2.t1 IS NULL
结果如下：
SQL> SELECT2 t1.t1 AS t1_t1,3 t1.t2 AS t1_t2,4 t1.t3 AS t1_t3,5 t2.t1 AS t2_t1,6 t2.t2 AS t2_t2,7 t2.t3 AS t2_t38 FROM9 t1 FULL JOIN t2 ON10 ( t1.t1 = t2.t111 AND t1.t2 = t2.t212 AND t1.t3 = t2.t313 )14 WHERE15 t1.t1 IS NULL16 OR t2.t1 IS NULL;T T1_T2 T1_T3 T T2_T2 T2_T3- ----- ---------- - ----- ----------9 x 9x8 Y 8YSQL>
是不是很简单呢~~
其实它的语句可以写成：
SELECT *FROM t1 full join t2 on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3)where t1.t1 is null or t2.t1 is null;
其中值得学习的是“FROM t1 full join t2 on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3)”，这是所有联合，不匹配的记录也显示出来。
我试图用(+)的方法执行，未能成功。可能(+)方式不能适用这种方法：
select * from t1,t2where t1.t1(+)=t2.t1(+) and t1.t2(+)=t2.t2(+) and t1.t3(+)=t2.t3(+)
执行如下：
SQL> SELECT *2 FROM t1 full join t23 on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3);T T2 T3 T T2 T3- ----- ---------- - ----- ----------1 a 1a 1 a 1a2 b 2b 2 b 2b3 c 3c 3 c 3c9 x 9x8 Y 8YSQL> SELECT *2 FROM t1 full join t23 on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3)4 where t1.t1 is null5 or t2.t1 is null;T T2 T3 T T2 T3- ----- ---------- - ----- ----------9 x 9x8 Y 8YSQL>
Tag: Oracle TTTBLOG