Oracle数据库怎么样比较两个表的数据是否一样?A,B两表结构一样,A表和B表比较,如果有数据不一样,则把不一样的数据保存到C表。
如:
A表数据
6,Mixed,Nuts,Z
B表数据
6,Mixed,Nuts,C要把两条数据都保存到C表,并且要标记出哪条数据是哪个表的,如:
source type user_id first_name last_name grade
A表 3 6 Mixed Nuts Z
B表 3 6 Mixed Nuts Csource 字段的意思是:数据来源哪个表
type 字段的意思是:
如果A表有的数据B表没有,则type=1。
如果B表有的数据A表没有,则type=2。
如果A表有的数据B表也有,则type=3。按照下列数据,最后C表的结果应该是:
source type user_id first_name last_name grade
A表 3 6 Mixed Nuts Z
B表 3 6 Mixed Nuts C
B表 2 11 Jack Fancy AA,B表结构:
create table A(
user_id integer not null,
first_name varchar(20),
last_name varchar(20),
grade varchar(20),
constraint A_pkey primary key(user_id)
)C表结构:
create table C(
source varchar(20) not null,
type integer not null,
user_id integer not null,
first_name varchar(20),
last_name varchar(20),
grade varchar(20)
)insert into A(user_id,first_name,last_name,grade)values(1,'Some','Dude','A')
insert into A(user_id,first_name,last_name,grade)values(2,'Other','Guy','B')
insert into A(user_id,first_name,last_name,grade)values(3,'You are','Welcome','B')
insert into A(user_id,first_name,last_name,grade)values(4,'What','Other','A')
insert into A(user_id,first_name,last_name,grade)values(5,'INeed','You','C')
insert into A(user_id,first_name,last_name,grade)values(6,'Mixed','Nuts','Z')
insert into A(user_id,first_name,last_name,grade)values(7,'Kirk','Land','B')
insert into A(user_id,first_name,last_name,grade)values(8,'Bit','Shooter,'A')
insert into A(user_id,first_name,last_name,grade)values(9,'Sun','Microsystem','C')
insert into A(user_id,first_name,last_name,grade)values(10,'Extra','Fancy','B')
insert into B(user_id,first_name,last_name,grade)values(1,'Some','Dude','A')
insert into B(user_id,first_name,last_name,grade)values(2,'Other','Guy','B')
insert into B(user_id,first_name,last_name,grade)values(3,'You are','Welcome','B')
insert into B(user_id,first_name,last_name,grade)values(4,'What','Other','A')
insert into B(user_id,first_name,last_name,grade)values(5,'INeed','You','C')
insert into B(user_id,first_name,last_name,grade)values(6,'Mixed','Nuts','C')
insert into B(user_id,first_name,last_name,grade)values(7,'Kirk','Land','B')
insert into B(user_id,first_name,last_name,grade)values(8,'Bit','Shooter,'A')
insert into B(user_id,first_name,last_name,grade)values(9,'Sun','Microsystem','C')
insert into B(user_id,first_name,last_name,grade)values(10,'Extra','Fancy','B')
insert into B(user_id,first_name,last_name,grade)values(11,'Jack','Fancy','B')
如:
A表数据
6,Mixed,Nuts,Z
B表数据
6,Mixed,Nuts,C要把两条数据都保存到C表,并且要标记出哪条数据是哪个表的,如:
source type user_id first_name last_name grade
A表 3 6 Mixed Nuts Z
B表 3 6 Mixed Nuts Csource 字段的意思是:数据来源哪个表
type 字段的意思是:
如果A表有的数据B表没有,则type=1。
如果B表有的数据A表没有,则type=2。
如果A表有的数据B表也有,则type=3。按照下列数据,最后C表的结果应该是:
source type user_id first_name last_name grade
A表 3 6 Mixed Nuts Z
B表 3 6 Mixed Nuts C
B表 2 11 Jack Fancy AA,B表结构:
create table A(
user_id integer not null,
first_name varchar(20),
last_name varchar(20),
grade varchar(20),
constraint A_pkey primary key(user_id)
)C表结构:
create table C(
source varchar(20) not null,
type integer not null,
user_id integer not null,
first_name varchar(20),
last_name varchar(20),
grade varchar(20)
)insert into A(user_id,first_name,last_name,grade)values(1,'Some','Dude','A')
insert into A(user_id,first_name,last_name,grade)values(2,'Other','Guy','B')
insert into A(user_id,first_name,last_name,grade)values(3,'You are','Welcome','B')
insert into A(user_id,first_name,last_name,grade)values(4,'What','Other','A')
insert into A(user_id,first_name,last_name,grade)values(5,'INeed','You','C')
insert into A(user_id,first_name,last_name,grade)values(6,'Mixed','Nuts','Z')
insert into A(user_id,first_name,last_name,grade)values(7,'Kirk','Land','B')
insert into A(user_id,first_name,last_name,grade)values(8,'Bit','Shooter,'A')
insert into A(user_id,first_name,last_name,grade)values(9,'Sun','Microsystem','C')
insert into A(user_id,first_name,last_name,grade)values(10,'Extra','Fancy','B')
insert into B(user_id,first_name,last_name,grade)values(1,'Some','Dude','A')
insert into B(user_id,first_name,last_name,grade)values(2,'Other','Guy','B')
insert into B(user_id,first_name,last_name,grade)values(3,'You are','Welcome','B')
insert into B(user_id,first_name,last_name,grade)values(4,'What','Other','A')
insert into B(user_id,first_name,last_name,grade)values(5,'INeed','You','C')
insert into B(user_id,first_name,last_name,grade)values(6,'Mixed','Nuts','C')
insert into B(user_id,first_name,last_name,grade)values(7,'Kirk','Land','B')
insert into B(user_id,first_name,last_name,grade)values(8,'Bit','Shooter,'A')
insert into B(user_id,first_name,last_name,grade)values(9,'Sun','Microsystem','C')
insert into B(user_id,first_name,last_name,grade)values(10,'Extra','Fancy','B')
insert into B(user_id,first_name,last_name,grade)values(11,'Jack','Fancy','B')
type 字段的意思是:
如果A表有的数据B表没有,则type=1。
如果B表有的数据A表没有,则type=2。
如果A表有的数据B表也有,只是有些字段的值不一样,则type=3。
merge into 可以实现
with t1 as (SELECT * FROM a MINUS SELECT * FROM b),
t2 as (SELECT * FROM b MINUS SELECT * FROM a)
select 'A' source,3 type,t1.* from t1 where exists (select 1 from t2 where t2.user_id=t1.user_id)
union all
select 'A' source,1 type,t1.* from t1 where not exists (select 1 from t2 where t2.user_id=t1.user_id)
union all
select 'A' source,3 type,t2.* from t2 where exists (select 1 from t1 where t2.user_id=t1.user_id)
union all
select 'A' source,2 type,t2.* from t2 where not exists (select 1 from t1 where t2.user_id=t1.user_id));
--你再测试下:
--创建过程实现:
CREATE OR REPLACE PROCEDURE insert_c
AS
cnt_a NUMBER;
cnt_b NUMBER;TYPE ab IS RECORD (
a_user_id a.user_id%TYPE,
a_first_name a.first_name%TYPE,
a_last_name a.last_name%TYPE,
a_grade a.grade%TYPE,
b_user_id b.user_id%TYPE,
b_first_name b.first_name%TYPE,
b_last_name b.last_name%TYPE,
b_grade b.grade%TYPE) ;
rs ab;
CURSOR cur IS
SELECT * FROM (
SELECT a.user_id AS a_user_id, a.first_name AS a_first_name,a.last_name AS a_last_name, a.grade AS a_grade,
b.user_id AS b_user_id, b.first_name AS b_first_name,b.last_name AS b_last_name, b.grade AS b_grade
FROM (SELECT a.* FROM a) a full join (SELECT b.* FROM b) b
ON a.user_id=b.user_id AND a.first_name=b.first_name
AND a.last_name=b.last_name AND a.grade= b.grade
)
WHERE a_user_id IS NULL OR b_user_id IS NULL ;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
IF rs.a_user_id IS NOT NULL AND rs.b_user_id IS NULL THEN
BEGIN
SELECT Count(1) INTO cnt_b FROM b WHERE user_id=rs.a_user_id ;
EXCEPTION WHEN OTHERS THEN
cnt_b:=0;
END;
IF cnt_b =0 THEN
INSERT INTO c VALUES('A表',1,rs.A_USER_ID,rs.A_FIRST_NAME,rs.A_LAST_NAME,rs.A_GRADE);
ELSE
INSERT INTO c VALUES('A表',3,rs.A_USER_ID,rs.A_FIRST_NAME,rs.A_LAST_NAME,rs.A_GRADE);
END IF;
END IF;
IF rs.b_user_id IS NOT NULL AND rs.a_user_id IS NULL THEN
BEGIN
SELECT Count(1) INTO cnt_a FROM a WHERE user_id=rs.b_user_id ;
EXCEPTION WHEN OTHERS THEN
cnt_a:=0;
END;
IF cnt_a =0 THEN
INSERT INTO c VALUES('B表',2,rs.B_USER_ID,rs.B_FIRST_NAME,rs.B_LAST_NAME,rs.B_GRADE);
ELSE
INSERT INTO c VALUES('B表',3,rs.B_USER_ID,rs.B_FIRST_NAME,rs.B_LAST_NAME,rs.B_GRADE);
END IF;
END IF;
END LOOP;
CLOSE cur;
END;
--测试:
EXEC INSERT_c;SELECT * FROM c;SOURCE TYPE USER_ID FIRST_NAME LAST_NAME GRADE
----------------------------------------------------------------
A表 3 6 Mixed Nuts Z
B表 2 11 Jack Fancy B
B表 3 6 Mixed Nuts C
select * from A
minus
select * from B* 换成你需要比对的字段
insert into c
select 'B',2,user_id,first_name,last_name,grade from b where not exists (select * from a where a.user_id=b.user_id);--a有,b没有
insert into c
select 'A',1,user_id,first_name,last_name,grade from a where not exists (select * from b where b.user_id=a.user_id);--a有,b有,但值不同
insert into c
select 'ab',3,d.user_id,d.first_name,d.last_name,d.grade
from
(select * from a
minus
select * from b) d;这个可以吧!楼主你试试