A，B两表结构一样，A表和B表比较，如果有数据不一样，则把不一样的数据保存到C表。如： A表数据 6,Mixed,Nuts,Z B表数据 6,Mixed,Nuts,C要把两条数据都保存到C表，并且要标记出哪条数据是哪个表的，如：source type user_id first_name last_name gradeA表 3 6 Mixed Nuts ZB表 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 gradeA表 3 6 Mixed Nuts ZB表 3 6 Mixed Nuts CB表 2 11 Jack Fancy AA，B表结构：create table A(user_id integer notnull,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) notnull,type integer notnull,user_id integer notnull,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 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(11,'Jack','Fancy','B')
insertinto c select*from (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 whereexists (select1from t2 where t2.user_id=t1.user_id)unionallselect'A' source,1 type,t1.*from t1 wherenotexists (select1from t2 where t2.user_id=t1.user_id)unionallselect'A' source,3 type,t2.*from t2 whereexists (select1from t1 where t2.user_id=t1.user_id)unionallselect'A' source,2 type,t2.*from t2 wherenotexists (select1from t1 where t2.user_id=t1.user_id));
解决方法:
