求表格数据合并的脚本(紧急)
两个字段完全相同的表格 A,B 的数据合并，如果名称相同的，则字段相加，如果没有的就新增
A表：Name, Money
a 100
b 200
B表：Name, Money
a 150
c 200
----------------------
合并到A表后
A表：Name, Money
a 250
b 200
c 200
[最优解释]
update A set money=b.money
from A inner join (
select name,SUM(money) money
from(
select name,money
from A
union all
select name,money
from B) as c
group by name) as d on a.name=d.name
insert into A(name,money)
select *
from B where not name in (select name from A)
[其他解释]
在1楼的基础上继续加字段咯
UPDATE A
SET money = b.money ,
num = b.num
FROM A
INNER JOIN ( SELECT name ,
SUM(money) money ,
SUM(num) num
FROM ( SELECT name ,
money ,
num
FROM A
UNION ALL
SELECT name ,
money ,
num
FROM B
) AS c
GROUP BY name
) AS d ON a.name = d.name
INSERT INTO A
( name ,
money ,
num
)
SELECT *
FROM B
WHERE NOT name IN ( SELECT name
FROM A )
[其他解释]
先谢过了，测试下能不能用先
[其他解释]
对了，如果有多个合并的字段呢？
A表：Name, Money, Num
a 100 1
b 200 2
B表：Name, Money, Num
a 150 2
c 200 2
----------------------
合并到A表后
A表：Name, Money, Num
a 250 3
b 200 2
c 200 2
[其他解释]
可以继续加：
update A set money=b.money,num=b.num from A inner join ( select name,SUM(money) money,sum(num) num from( select name,money,num from A union all select name,money,num from B) as c group by name) as d on a.name=d.name insert into A(name,money,num) select * from B where not name in (select name from A)
[其他解释]
顶。。。
------其他解决方案--------------------
Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.money"。
Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.num"。
有错误啊
[其他解释]
c.money,c.num 搞错了
[其他解释]
d.money,d.num 呵呵 晕死了
[其他解释]
哦，成功了，结贴给分
[其他解释]
---------------------创建表以及插入数据，开始-----------------------------------
if(object_id('a') is not null) drop table a
go
create table a
(
[name] varchar(20),
[money] int
)
go
insert into a
select 'a',100 union all
select 'b',200
go
if(object_id('b')is not null)drop table b
go
create table b
(
[name] varchar(20),
[Money] int
)
go
insert into b
select 'a',150 union all
select 'c',200
go
---------------------创建表以及插入数据，结束-----------------------------------
--开始SELECT
select case when a.[name] is null then b.name else a.[name] end as [name],isnull(a.[money],0)+isnull(b.[money],0)as [money]
from a full outer join b on a.name = b.name
--结果展示
/*
name money
-------------------- -----------
a 250
b 200
c 200
(3 行受影响)
*/
[其他解释]
---------------------创建表以及插入数据，开始-----------------------------------
if(object_id('a') is not null) drop table a
go
create table a
(
[name] varchar(20),
[money] int
)
go
insert into a
select 'a',100 union all
select 'b',200
go
if(object_id('b')is not null)drop table b
go
create table b
(
[name] varchar(20),
[Money] int
)
go
insert into b
select 'a',150 union all
select 'c',200
go
---------------------创建表以及插入数据，结束-----------------------------------
--开始SELECT
select case when a.[name] is null then b.name else a.[name] end as [name],isnull(a.[money],0)+isnull(b.[money],0)as [money]
from a full outer join b on a.name = b.name
--结果展示
/*
name money
-------------------- -----------
a 250
b 200
c 200
(3 行受影响)
*/
--开始更新
update a set money = b.money+a.money from a inner join b on b.name = a.name
--查看更新结果
select * from a
/*
name money
-------------------- -----------
a 250
b 200
(2 行受影响)
*/
insert into a
select * from b where not exists (select 1 from a where a.name = b.name)
--查看插入结果
select * from a
/*
name money
-------------------- -----------
a 250
b 200
c 200
(3 行受影响)
*/