Allen 2010-11-09 8258 0 0 0 0
有这样一个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 - Production
With 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     1a
2 b     2b
3 c     3c
9 x     9x
 
SQL> select * from t2;
 
T T2    T3
- ----- ----------
1 a     1a
2 b     2b
3 c     3c
8 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     1a
2 b     2b         2 b     2b
3 c     3c         3 c     3c
9 x     9x
 
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     1a
2 b     2b         2 b     2b
3 c     3c         3 c     3c
                   8 Y     8Y
 
SQL> 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     9x
 
SQL> 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     9x
8 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     9x
8 Y     8Y
 
SQL>
 
--OK
 
但,另有高人一个语句就查出来了,不用这么麻烦了!
 
SELECT 
  t1.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_t3 
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
 
结果如下:
 
SQL> SELECT
  2    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_t3
  8  FROM
  9    t1 FULL JOIN t2 ON
 10      ( t1.t1 = t2.t1
 11        AND t1.t2 = t2.t2
 12        AND t1.t3 = t2.t3
 13      )
 14  WHERE
 15    t1.t1 IS NULL
 16    OR t2.t1 IS NULL;
 
T T1_T2 T1_T3      T T2_T2 T2_T3
- ----- ---------- - ----- ----------
9 x     9x
                   8 Y     8Y
 
SQL>
 
是不是很简单呢~~
其实它的语句可以写成:
 
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,t2 
where t1.t1(+)=t2.t1(+) and t1.t2(+)=t2.t2(+) and t1.t3(+)=t2.t3(+)
 执行如下:
SQL> SELECT *
  2  FROM t1 full join t2
  3    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     1a
2 b     2b         2 b     2b
3 c     3c         3 c     3c
9 x     9x
                   8 Y     8Y
 
SQL> SELECT *
  2  FROM t1 full join t2
  3    on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3)
  4  where t1.t1 is null
  5     or t2.t1 is null;
 
T T2    T3         T T2    T3
- ----- ---------- - ----- ----------
9 x     9x
                   8 Y     8Y
 
SQL>
 
 

【版权声明】
本文为原创,遵循CC 4.0 BY-SA版权协议!转载请附上原文出处链接及本声明。
原文链接:https://tdlib.com/am.php?t=cMOCmfQMfUNu
Tag: Oracle TTTBLOG
我也要发一个   ·   返回首页     ·   返回[Oracle]   ·    前一个  ·   下一个
未登录,
请先 [ 注册 ] or [ 登录 ]
(一分钟即可完成注册!)
返回首页     ·   返回[Oracle]   ·   返回顶部