合并表，同时合并部分数据

[kdg2000]

有两个表 T1 T2

T1
name count
A 3
B 5
C 3

T2
name count
A 2
B 3
D 4

得到如下表T3
name count
A 5
B 8
C 3
D 4

把两个表合并，按name分组，并把count值相加
越简单越好，谢谢

[江城007]

select name, sum(count) as count
from (
select name, count
from t1
union
select name, count
) as t
group by name

[江城007]

select name, sum(count) as count
from (
select name, count
from t1
union
select name, count
from t2
) as t
group by name

[ojuju10]

create table t1 (name varchar(10),count int)
insert into t1 select 'a',3
union all select 'b',5
union all select 'c',3

create table t2 (name varchar(10),count int)
insert into t2 select 'a',2
union all select 'b',3
union all select 'd',5


select name,sum([count]) as count
from (
select * from t1
union all select * from t2
) dd
group by name

name count
---------- -----------
a 5
b 8
c 3
d 5

（所影响的行数为 4 行）

drop table t1,t2

[cy36279211]

paoluo的的方法可以用来合并两张以上的表．

[sp4]

create table T1 (name varchar(100), count int)
insert into T1
select 'A',3 union all
select 'B',5 union all
select 'C',3

create table T2 (name varchar(100), count int)
insert into T2
select 'A',2 union all
select 'B',3 union all
select 'D',5


SELECT NAME,SUM(COUNT) AS SUM1 FROM (
SELECT * FROM T1
UNION ALL
SELECT * FROM T2) AS T
GROUP BY NAME

DROP TABLE T1 DROP TABLE T2

[paoluo]

可以不需要用的union，直接用full join

Select
IsNull(A.Name, B.Name) As Name,
IsNull(A.[count], 0) + IsNull(B.[count], 0) As [count]
From
A
Full Join
B
On A.Name = B.Name

[gaoninggao]

create table t1(name varchar(3),con int)
insert t1 select 'a',3
union all select 'b',5
union all select 'c',3
create table t2(name varchar(3),con int)
insert t2 select 'a',2
union all select 'b',3
union all select 'd',4

select name ,sum(con) as con from(select * from t1
union all
select * from t2) t group by name
drop table t1
drop table t2

[believe209]

select name,sum([count]) as count
from (
select * from t1
union all select * from t2
) tmp
group by name
-------------------------------
佩服 paoluo(一天到晚游泳的鱼)
给人多种思路
顶!

[sp4]

full join不错

[saiwong]

select name,sum([count]) as count
from (
select * from t1
union all select * from t2
) tmp
group by name