# 两个表相加，该怎么处理

www.myexceptions.net  网友分享于：2013-03-10  浏览：14次

aaa         10                                 ccc         15
bbb         20                                 ddd         30
ccc         20

aaa         10         0
bbb         20         0
ccc         20         15
ddd         0           30

------解决方案--------------------
declare @t1 table(a char(10),b int)
insert into @t1 select 'aaa ',10 union all
select 'bbb ',20 union all
select 'ccc ',20
declare @t2 table(a char(10),b int)
insert into @t2 select 'ccc ',15 union all
select 'ddd ',30
select isnull(a.a,b.a),isnull(a.b,0),isnull(b.b,0) from @t1 a full join @t2 b on a.a=b.a
order by isnull(a.a,b.a)
resu---------- ----------- -----------
aaa 10 0
bbb 20 0
ccc 20 15
ddd 0 30

（所影响的行数为 4 行）lt:

------解决方案--------------------
create table T1(col1 varchar(10), col2 int)
insert T1 select 'aaa ', 10
insert T1 select 'bbb ', 20
insert T1 select 'ccc ', 20

create table T2(col1 varchar(10), col2 int)
insert T2 select 'ccc ', 15
insert T2 select 'ddd ', 30

select col1, col2=sum(col2), col3=sum(col3)
from
(
select col1, col2, col3=0 from T1
union all
select col1, col2=0, col3=col2 from T2
)tmp
group by col1

---result
col1 col2 col3
---------- ----------- -----------
aaa 10 0
bbb 20 0
ccc 20 15
ddd 0 30

(4 row(s) affected)