是以当前表为对照表吗 ? with tableA as ( select '201309' ym,'张三' name,'01' nameid,'济南' adress from dual union all select '201309' ym,'丢丢' name,'02' nameid,'北京' adress from dual ),tableB as ( select '201308' ym,'李四' name,'01' nameid,'南京' adress from dual union all select '201308' ym,'豆豆' name,'02' nameid,'北京' adress from dual )select ym,nameid,'A' tbCode,'name' fdCode,n1 last,n2 end from ( select a.nameid,a.ym,a.name n1,b.name n2 from tableA a left join tableB b on a.nameid = b.nameid where a.name<>b.name ) union all select ym,nameid,'A' tbCode,'adress' fdCode,a1 last,a2 end from ( select a.nameid,a.ym,a.adress a1,b.adress a2 from tableA a left join tableB b on a.nameid = b.nameid where a.adress<>b.adress) ym nameid tbCode fdCode last end ----------------------------------------------------------- 1 201309 01 A name 张三 李四 2 201309 02 A name 丢丢 豆豆 3 201309 01 A adress 济南 南京
对的 nameId 相同 就计入 C表
那就按照上面写的插入到表C就可以咯 insert into tableCselect ym,nameid,tbCode,fdCode,last,end from ( select ym,nameid,'A' tbCode,'name' fdCode,n1 last,n2 end from ( select a.nameid,a.ym,a.name n1,b.name n2 from tableA a left join tableB b on a.nameid = b.nameid where a.name<>b.name ) union all select ym,nameid,'A' tbCode,'adress' fdCode,a1 last,a2 end from ( select a.nameid,a.ym,a.adress a1,b.adress a2 from tableA a left join tableB b on a.nameid = b.nameid where a.adress<>b.adress) )
再给个另一种方法:create table A(ym int, name varchar2(12), nameId varchar2(10), address varchar2(100)); create table B(ym int, name varchar2(12), nameId varchar2(10), address varchar2(100)); create table C( ym int, nameId varchar2(10), tbCode varchar2(10), fdCode varchar2(15), last varchar2(100), end varchar2(100) );
insert into A(ym, name, nameId, address) values(201309, '张三', '01', '济南'); insert into B(ym, name, nameId, address) values(201309, '李四', '01', '泰安');declare cursor c1 is select a.*, b.name b_name, b.address b_address from A, B where a.nameId=b.nameId and a.ym=b.ym; v_sql varchar2(1024); v_sql_pre varchar2(100); begin for c1_res in c1 loop v_sql_pre := 'insert into c(ym, nameId, tbCode, fdCode, last, end) values('||c1_res.ym||', '''||c1_res.nameId||''', ''A'', '; if c1_res.name != c1_res.b_name then v_sql := v_sql_pre||'''name'', '''||c1_res.name||''', '''||c1_res.b_name||''')'; execute immediate v_sql; end if; if c1_res.address != c1_res.b_address then v_sql := v_sql_pre||'''address'', '''||c1_res.address||''', '''||c1_res.b_address||''')'; execute immediate v_sql; end if; end loop; end; /select * from c;
那就按照上面写的插入到表C就可以咯 insert into tableCselect ym,nameid,tbCode,fdCode,last,end from ( select ym,nameid,'A' tbCode,'name' fdCode,n1 last,n2 end from ( select a.nameid,a.ym,a.name n1,b.name n2 from tableA a left join tableB b on a.nameid = b.nameid where a.name<>b.name ) union all select ym,nameid,'A' tbCode,'adress' fdCode,a1 last,a2 end from ( select a.nameid,a.ym,a.adress a1,b.adress a2 from tableA a left join tableB b on a.nameid = b.nameid where a.adress<>b.adress) ) 我要是一个表有三四十个字段那不要对比三四十次啊 ! SQL太大了吧?唉!!
with tableA as
(
select '201309' ym,'张三' name,'01' nameid,'济南' adress from dual union all
select '201309' ym,'丢丢' name,'02' nameid,'北京' adress from dual
),tableB as
(
select '201308' ym,'李四' name,'01' nameid,'南京' adress from dual union all
select '201308' ym,'豆豆' name,'02' nameid,'北京' adress from dual
)select ym,nameid,'A' tbCode,'name' fdCode,n1 last,n2 end
from (
select a.nameid,a.ym,a.name n1,b.name n2
from tableA a left join tableB b on a.nameid = b.nameid
where a.name<>b.name )
union all
select ym,nameid,'A' tbCode,'adress' fdCode,a1 last,a2 end
from (
select a.nameid,a.ym,a.adress a1,b.adress a2
from tableA a left join tableB b on a.nameid = b.nameid
where a.adress<>b.adress) ym nameid tbCode fdCode last end
-----------------------------------------------------------
1 201309 01 A name 张三 李四
2 201309 02 A name 丢丢 豆豆
3 201309 01 A adress 济南 南京
对的 nameId 相同 就计入 C表
那就按照上面写的插入到表C就可以咯
insert into tableCselect ym,nameid,tbCode,fdCode,last,end
from
(
select ym,nameid,'A' tbCode,'name' fdCode,n1 last,n2 end
from (
select a.nameid,a.ym,a.name n1,b.name n2
from tableA a left join tableB b on a.nameid = b.nameid
where a.name<>b.name )
union all
select ym,nameid,'A' tbCode,'adress' fdCode,a1 last,a2 end
from (
select a.nameid,a.ym,a.adress a1,b.adress a2
from tableA a left join tableB b on a.nameid = b.nameid
where a.adress<>b.adress)
)
create table B(ym int, name varchar2(12), nameId varchar2(10), address varchar2(100));
create table C(
ym int, nameId varchar2(10), tbCode varchar2(10),
fdCode varchar2(15), last varchar2(100), end varchar2(100)
);
insert into A(ym, name, nameId, address) values(201309, '张三', '01', '济南');
insert into B(ym, name, nameId, address) values(201309, '李四', '01', '泰安');declare
cursor c1 is
select a.*, b.name b_name, b.address b_address
from A, B
where a.nameId=b.nameId and a.ym=b.ym;
v_sql varchar2(1024);
v_sql_pre varchar2(100);
begin
for c1_res in c1 loop
v_sql_pre := 'insert into c(ym, nameId, tbCode, fdCode, last, end) values('||c1_res.ym||', '''||c1_res.nameId||''', ''A'', ';
if c1_res.name != c1_res.b_name then
v_sql := v_sql_pre||'''name'', '''||c1_res.name||''', '''||c1_res.b_name||''')';
execute immediate v_sql;
end if;
if c1_res.address != c1_res.b_address then
v_sql := v_sql_pre||'''address'', '''||c1_res.address||''', '''||c1_res.b_address||''')';
execute immediate v_sql;
end if;
end loop;
end;
/select * from c;
那就按照上面写的插入到表C就可以咯
insert into tableCselect ym,nameid,tbCode,fdCode,last,end
from
(
select ym,nameid,'A' tbCode,'name' fdCode,n1 last,n2 end
from (
select a.nameid,a.ym,a.name n1,b.name n2
from tableA a left join tableB b on a.nameid = b.nameid
where a.name<>b.name )
union all
select ym,nameid,'A' tbCode,'adress' fdCode,a1 last,a2 end
from (
select a.nameid,a.ym,a.adress a1,b.adress a2
from tableA a left join tableB b on a.nameid = b.nameid
where a.adress<>b.adress)
)
我要是一个表有三四十个字段那不要对比三四十次啊 ! SQL太大了吧?唉!!