我在两个不同的Oracle数据库中有两个相似的表。
例如:我的表名为EMPLOYEE,主键为员工ID。这两个数据库中都有相同的表和列(假设有50列),并且这两个数据库已经连接。我想逐列比较这两个表,并找出哪些记录不匹配。我想知道两个表中每行的具体列不匹配。
我在两个不同的Oracle数据库中有两个相似的表。
例如:我的表名为EMPLOYEE,主键为员工ID。这两个数据库中都有相同的表和列(假设有50列),并且这两个数据库已经连接。我想逐列比较这两个表,并找出哪些记录不匹配。我想知道两个表中每行的具体列不匹配。
select *
from
(
( select * from TableInSchema1
minus
select * from TableInSchema2)
union all
( select * from TableInSchema2
minus
select * from TableInSchema1)
)
如果你想通过查询来解决这个问题,should do the trick
就可以了。
SELECT col1
, col2
-- (include all columns that you want to compare)
, COUNT(src1) CNT1
, COUNT(src2) CNT2
FROM (SELECT a.col1
, a.col2
-- (include all columns that you want to compare)
, 1 src1
, TO_NUMBER(NULL) src2
FROM tab_a a
UNION ALL
SELECT b.col1
, b.col2
-- (include all columns that you want to compare)
, TO_NUMBER(NULL) src1
, 2 src2
FROM tab_b b
)
GROUP BY col1
, col2
HAVING COUNT(src1) <> COUNT(src2) -- only show the combinations that don't match
感谢这里的贡献:http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1417403971710
虽然这不会很快,而且你需要输入很多内容(除非你从user_tab_columns生成SQL),但以下是我在需要逐行和逐列比较两个表时使用的方法。
此查询将返回:
(相同的行将被排除)。
"PK"是组成您的主键的列。 "a"将包含A,如果当前行存在于table1中。 "b"将包含B,如果当前行存在于table2中。
select pk
,decode(a.rowid, null, null, 'A') as a
,decode(b.rowid, null, null, 'B') as b
,a.col1, b.col1
,a.col2, b.col2
,a.col3, b.col3
,...
from table1 a
full outer
join table2 b using(pk)
where decode(a.col1, b.col1, 1, 0) = 0
or decode(a.col2, b.col2, 1, 0) = 0
or decode(a.col3, b.col3, 1, 0) = 0
or ...;
编辑 添加示例代码以展示评论中描述的差异。 每当其中一个值包含NULL时,结果将不同。
with a as(
select 0 as col1 from dual union all
select 1 as col1 from dual union all
select null as col1 from dual
)
,b as(
select 1 as col1 from dual union all
select 2 as col1 from dual union all
select null as col1 from dual
)
select a.col1
,b.col1
,decode(a.col1, b.col1, 'Same', 'Different') as approach_1
,case when a.col1 <> b.col1 then 'Different' else 'Same' end as approach_2
from a,b
order
by a.col1
,b.col1;
col1 col1_1 approach_1 approach_2
==== ====== ========== ==========
0 1 Different Different
0 2 Different Different
0 null Different Same <---
1 1 Same Same
1 2 Different Different
1 null Different Same <---
null 1 Different Same <---
null 2 Different Same <---
null null Same Same
使用减号
运算符是可行的,但执行时间较长,这是不可接受的。我有一个类似数据迁移的需求,我用NOT IN
运算符来解决。修改后的查询语句是:
select *
from A
where (emp_id,emp_name) not in
(select emp_id,emp_name from B)
union all
select * from B
where (emp_id,emp_name) not in
(select emp_id,emp_name from A);
这个查询执行得很快。同时,您可以在选择查询中添加任意数量的列。 唯一的限制是两个表必须具有完全相同的表结构才能执行此操作。
SELECT *
FROM (SELECT table_name, COUNT (*) cnt
FROM all_tab_columns
WHERE owner IN ('OWNER_A')
GROUP BY table_name) x,
(SELECT table_name, COUNT (*) cnt
FROM all_tab_columns
WHERE owner IN ('OWNER_B')
GROUP BY table_name) y
WHERE x.table_name = y.table_name AND x.cnt <> y.cnt;
使用了全外连接 -- 但如果没有匹配,它将不会显示 -
SQL> desc aaa - 这是一个表 名称 是否为空? 类型
A1 数字 B1 VARCHAR2(10)
SQL> 描述 aaav - 这是一个视图 名称 是否为空? 类型
A1 数字 B1 VARCHAR2(10)
SQL> 选择 a.column_name,b.column_name from dba_tab_columns a full outer join dba_tab_columns b on a.column_name=b.column_name where a.TABLE_NAME='AAA' and B.table_name='AAAV';
COLUMN_NAME COLUMN_NAME
A1 A1 B1 B1
A1 A1 B1 B1