表A tblA
f1 f2 fQty
1 2 90
1 3 80
2 3 72表B tblB
f1 f2 fQty
2 3 8
1 2 89
2 3 80
首先根据表B中的记录(条件为tblA.f1=tblB.f1 and tblA.f2=tblB.f2)
,从表A中搜索相应的记录,如果存在,则将表B的fQty加入到表A的fQty
,如果记录不存在的话,则将表B中的记录插入到表A中操作后表A的结果为
f1 f2 fQty
1 2 179
1 3 80
2 3 160
f1 f2 fQty
1 2 90
1 3 80
2 3 72表B tblB
f1 f2 fQty
2 3 8
1 2 89
2 3 80
首先根据表B中的记录(条件为tblA.f1=tblB.f1 and tblA.f2=tblB.f2)
,从表A中搜索相应的记录,如果存在,则将表B的fQty加入到表A的fQty
,如果记录不存在的话,则将表B中的记录插入到表A中操作后表A的结果为
f1 f2 fQty
1 2 179
1 3 80
2 3 160
t.f1,t.f2,sum(t.fQty) as fQty
from
(select * from tabA union all select * from tabB) t
group by
t.f1,t.f2
order by
t.f1,t.f2
SELECT * INTO tmp FROM
(
select f1,f2,sum(fQty )
from
(
SELECT f1 ,f2 , fQty FROM A
UNION ALL
SELECT f1 ,f2 , fQty FROM B
) a
) a
--合并及更新tblA中已存在的记录
update a
set
a.fQty=a.fQty+sum(b.fQty)
from
tblA a,tblB b
where
a.f1=b.f1 and a.f2=b.f2
group by
a.f1,a.f2
--合并及增加tblA中不存在的记录
insert into tblA(f1,f2,fQty)
select
f1,f2,sum(fQty)
from
tblB t
where
not exists(select 1 from tblA where f1=t.f1 and f2=t.f2)
group by
t.f1,t.f2
(
select f1,f2,sum(fQty )
from
(
SELECT f1 ,f2 , fQty FROM A
UNION ALL
SELECT f1 ,f2 , fQty FROM B
) a group by f1,f2
) a
set A.fQty = A.fQty + B.fQty
from A
inner join B
on A.f1=B.f1 and A.f2=B.f2
insert A
select B.*
from B
left join A
on A.f1=B.f1 and A.f2=B.f2
where A.f1 is null
--> 测试数据: #ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
create table #ta ([f1] int,[f2] int,[fQty] int)
insert into #ta
select 1,2,90 union all
select 1,3,80 union all
select 2,3,72
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb ([f1] int,[f2] int,[fQty] int)
insert into #tb
select 2,3,8 union all
select 1,2,89 union all
select 2,3,80
-->语句select
a.f1,a.f2,sum(a.fQty) as fQty
from
(select * from #ta union all select * from #tb) a
group by
a.f1,a.f2
order by
a.f1,a.f2
-->result
/*
f1 f2 fQty
----------- ----------- -----------
1 2 179
1 3 80
2 3 160
*/
-->删除测试表drop table #ta
drop table #tb
from
(select * from tbla union all select * from tblb) a
group by f1,f2
if object_id('tempdb.dbo.#ta') is not null drop table #ta
create table #ta ([f1] int,[f2] int,[fQty] int)
insert into #ta
select 1,2,90 union all
select 1,3,80 union all
select 2,3,72
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb ([f1] int,[f2] int,[fQty] int)
insert into #tb
select 2,3,8 union all
select 1,2,89 union all
select 2,3,80
-->语句select
a.f1,a.f2,sum(a.fQty) as fQty
from
(select * from #ta union all select * from #tb) a
group by
a.f1,a.f2
order by
a.f1,a.f2
-->result
/*
f1 f2 fQty
----------- ----------- -----------
1 2 179
1 3 80
2 3 160
*/
-->删除测试表drop table #ta
drop table #tb
set
a.fQty=a.fQty+sum(b.fQty)
from
tblA a,tblB b
where
a.f1=b.f1 and a.f2=b.f2
group by
a.f1,a.f2这句有错误的
(
f1 int,
f2 int,
fQty int
)
insert into tblA select 1,2,90
insert into tblA select 1,3,80
insert into tblA select 1,3,72
create table tblB
(
f1 int,
f2 int,
fQty int
)
insert into tblA select 2,3,8
insert into tblA select 1,2,89
insert into tblA select 2,3,80select
a.f1,a.f2,sum(a.fQty) as fQty
from
(select * from tblA union all select * from tblB) a
group by
a.f1,a.f2
order by
a.f1,a.f2
create table #ta ([f1] int,[f2] int,[fQty] int)
insert into #ta
select 1,2,90 union all
select 1,3,80 union all
select 2,3,72create table #tb ([f1] int,[f2] int,[fQty] int)
insert into #tb
select 2,3,8 union all
select 1,2,89 union all
select 2,3,80
update a set fqty= fqty+isnull((select
sum(fqty) from #tb where a.f1=f1 and a.f2=f2 ),0)
from #ta a
select * from #ta a/*
f1 f2 fQty
----------- ----------- -----------
1 2 179
1 3 80
2 3 160(所影响的行数为 3 行)
*/
insert into #ta
select 1,2,90 union all
select 1,3,80 union all
select 2,3,72create table #tb ([f1] int,[f2] int,[fQty] int)
insert into #tb
select 2,3,8 union all
select 1,2,89 union all
select 2,3,80select f1,f2,sum(fqty) fqty
from
(
select * from #ta
union all
select * from #tb b
where exists(select 1 from #ta a where a.f1=b.f1 and a.f2=b.f2)
) aa
group by f1,f2
drop table #ta,#tb/*
f1 f2 fQty
----------- ----------- -----------
1 2 179
1 3 80
2 3 160(所影响的行数为 3 行)
*/
set fqty=tbla.qty+tblb.qty
from tbla,tblb whree tbla.f1=tblb.f1 and tbla.f2=tblb.f2insert into tbla (f1,f2,fqty) select f1,f2,fqty from tblb where not exists(select * from tbla where f1=tblb.f1 and f2=tblb.f2)