update a
set
m3=b.m3
from
tab1 a,
(select m0,m1,m2,m3=sum(m3) from tab2) b
where
a.m0=b.m0 and a.m1=b.m1 and a.m2=b.m2insert into tab1
select
b.*
from
(select
m0,m1,m2,m3=sum(m3)
from
tab2 a
where
not exists(select 1 from tab1 where m0=a.m0 and m1=a.m1 and m2=a.m2)) b
set
m3=b.m3
from
tab1 a,
(select m0,m1,m2,m3=sum(m3) from tab2) b
where
a.m0=b.m0 and a.m1=b.m1 and a.m2=b.m2insert into tab1
select
b.*
from
(select
m0,m1,m2,m3=sum(m3)
from
tab2 a
where
not exists(select 1 from tab1 where m0=a.m0 and m1=a.m1 and m2=a.m2)) b
insert #tab1 select '001',01,'0001',89
union all select '001',02,'0001',99
union all select '001',02,'0002',50create table #tab2 (m0 char(20),m1 char(20),m2 char(20),m3 int )
insert #tab2 select '001',01,'0001',20
union all select '001',02,'0001',20
union all select '001',03,'0001',50
union all select '001',03,'0002',50
union all select '001',02,'0002',50
update a
set
n3=b.m3
from
#tab1 a,
(select m0,m1,m2,m3=sum(m3) from #tab2 group by m0,m1,m2) b
where
a.n0=b.m0 and a.n1=b.m1 and a.n2=b.m2insert into #tab1
select
b.*
from
(select
m0,m1,m2,m3=sum(m3)
from
#tab2 a
where
not exists(select 1 from #tab1 where n0=a.m0 and n1=a.m1 and n2=a.m2)
group by
m0,m1,m2,m3) bselect * from #tab1
from(
select m0,m1,m2,sum(m3) m3 from tab2
group by m0,m1,m2
)tab2
where tab1.n0=tab2.m0 and tab1.n1=tab2.m1 and tab1.n2=tab2.m2
create table #tab1 (n0 char(20),n1 char(20),n2 char(20),n3 int )
insert #tab1 select '001',01,'0001',89
union all select '001',02,'0001',99
union all select '001',02,'0002',50create table #tab2 (m0 char(20),m1 char(20),m2 char(20),m3 int )
insert #tab2 select '001',01,'0001',20
union all select '001',02,'0001',20
union all select '001',03,'0001',50
union all select '001',03,'0002',50
union all select '001',02,'0002',50--更新tab1中存在的记录
update a
set
n3=b.m3
from
#tab1 a,
(select m0,m1,m2,m3=sum(m3) from #tab2 group by m0,m1,m2) b
where
a.n0=b.m0 and a.n1=b.m1 and a.n2=b.m2
--插入tab1中不存在的记录
insert into #tab1
select
b.*
from
(select
m0,m1,m2,m3=sum(m3)
from
#tab2 a
where
not exists(select 1 from #tab1 where n0=a.m0 and n1=a.m1 and n2=a.m2)
group by
m0,m1,m2,m3) b--更新结果
select * from #tab1n1 n2 n3 n4
------ ------- ------ ------
001 1 0001 20
001 2 0001 20
001 2 0002 50
001 3 0001 50
001 3 0002 50
select * from tab2 b
where not exists(
select * from tab1 a
where a.n0=b.m0 and a.n1=b.m1 and a.n2=b.m2
)