请问如何做如下的查询
表a 表b
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)