Oracle

Allen 2010-11-09 14:30:03 34701 0 0 0 0

網站名稱：如何查询两个表中所有不匹配记录……

網站地址：

[SEO信息] [Alexa信息]

-->>直達網站

有这样一个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>