A和B表结构完全相同,两表中有主键重复
比如
id PK
age
name
mailA表
id age name mail
1 23 lilei [email protected]
2 hanmeimei
3 23 jim [email protected]
4 22 tomB表
id age name mail
1 23 lilei [email protected]
2 25 hanmeimei fff@124com
5 27 green [email protected]要求A表和B表合并成有id为1~5的表
以A表为准 如果A中没有的id,B表完全合并进去,
如果同一个ID,A中没某个字段没有值,那么用B表的值补进去,如果A中的某个字段有值,那么以A表为准。
比如
id PK
age
name
mailA表
id age name mail
1 23 lilei [email protected]
2 hanmeimei
3 23 jim [email protected]
4 22 tomB表
id age name mail
1 23 lilei [email protected]
2 25 hanmeimei fff@124com
5 27 green [email protected]要求A表和B表合并成有id为1~5的表
以A表为准 如果A中没有的id,B表完全合并进去,
如果同一个ID,A中没某个字段没有值,那么用B表的值补进去,如果A中的某个字段有值,那么以A表为准。
merge into a
using b on (a.id=b.id)
when not matched then
insert into (a.id,a.age,a.name,a.mail)
values(b.id,b.age,b.name,b.mail);如果同一个ID,A中没某个字段没有值,那么用B表的值补进去,如果A中的某个字段有值,那么以A表为准?
等楼下的吧
select 1 id, 23 age, 'lilei' name, '[email protected]' mail from dual union all
select 2, null, 'hanmeimei',null from dual union all
select 3, 23, null, 'jim [email protected]' from dual union all
select 4, 22, 'tom',null from dual
),
b as(
select 1 id, 23 age, 'lilei' name, '[email protected]' mail from dual union all
select 2, 25, 'hanmeimei', 'fff@124com' from dual union all
select 5, 27, 'green', '[email protected]' from dual
)
--以上是测试数据,sql如下:
select nvl(a.id,b.id) id,
nvl(a.age,b.age) age,
nvl(a.name,b.name) name,
nvl(a.mail,b.mail) mail
from a full join b on a.id=b.id
order by 1--result:
ID AGE NAME MAIL
--------------------------------
1 23 lilei [email protected]
2 25 hanmeimei fff@124com
3 23 jim [email protected]
4 22 tom
5 27 green [email protected]
insert into a values ('1',23,'lilei','[email protected]');
insert into a values ('2',null,'hanmeimei',null);
insert into a values ('3',23,'jim','[email protected]');
insert into a values ('4',22,'tom',null);
create table b as select * from a where 1=0;
insert into b values ('1',23,'lilei','[email protected]');
insert into b values ('2',25,'hanmeimei','[email protected]');
insert into b values ('5',27,'green', '[email protected]');
merge into a
using b on (a.id=b.id)
when matched then
update set age=COALESCE(a.age,b.age),name=COALESCE(a.name,b.name),mail=COALESCE(a.mail,b.mail)
when not matched then
insert (a.id,a.age,a.name,a.mail)
values(b.id,b.age,b.name,b.mail);select * from a;1 1 23 lilei [email protected]
2 2 25 hanmeimei [email protected]
3 3 23 jim [email protected]
4 4 22 tom
5 5 27 green [email protected]
您好 您好下面的merge的方案好像都可行 都让我受教了 我7万多条数据,不知道哪个效率更高些
谢谢 看在我凌晨发帖的痛苦上 原谅我吧
好像这种方案和楼上的full join都可以阿?
我这直接就插入更新到a表了,他那个只是select出结果
嗯 应该还是更新A表。请教下您这里COALESCE 能改成nvl吗?
可以,一样的效果,COALESCE返回第一个不为null的表达式
-- 更新语句:
update a
set (a.age,a.name,a.mail=(
select nvl(a.age,b.age), nvl(a.name,b.name), nvl(a.mail,b.mail)
from a a1, b b1
where a1.id=b1.id and a1.id=a.id )
where exists (select 1 from a a2 where a2.id=a.id);-- 插入语句:
insert into a(id,age,name,mail)
select b.id, b.age, b.name, b.mail
from b
where b.id not in (select a.id from a);A和B表结构完全相同,两表中有主键重复
比如
id PK
age
name
mailA表
id age name mail
1 23 lilei [email protected]
2 hanmeimei
3 23 jim [email protected]
4 22 tomB表
id age name mail
1 23 lilei [email protected]
2 25 hanmeimei fff@124com
5 27 green [email protected]
-- 当然:最高效的方法还是merge ... into ... !