求两数据表的数据合并查询

[Freeid_shi]

现在查询到两表的综合汇总数据，按Name取所有数据，Name相同的放一列，无total,money数据的为0
表A
name total money
a 11 22
b 1 23
表B
name total money
a 4 6
c 17 9
查询结果
name a.total a.money b.total b.money
a 11 22 4 6
b 1 23 0 0
c 0 0 17 9

[dawugui]

select isnull(a.name,b.name) name,
       isnull(a.total,0) a_total,
       isnull(a.money,0) a_money,
       isnull(b.total,0) b_total,
       isnull(b.money,0) b_money
from a full join b on a.name = b.name

[dawugui]

create table A(name varchar(10),total int,money int)
insert into A values('a' ,11, 22) 
insert into A values('b' ,1 , 23) 
create table B(name varchar(10),total int,money int)
insert into B values('a' ,4  ,6 )
insert into B values('c' ,17 ,9 )
go

select isnull(a.name,b.name) name,
       isnull(a.total,0) a_total,
       isnull(a.money,0) a_money,
       isnull(b.total,0) b_total,
       isnull(b.money,0) b_money
from a full join b on a.name = b.name
order by a.name

drop table A , B

/*
name       a_total     a_money     b_total     b_money     
---------- ----------- ----------- ----------- ----------- 
a          11          22          4           6
b          1           23          0           0
c          0           0           17          9

（所影响的行数为 3 行）

*/

[百年树人]

select 
  isnull(a.name,b.name) as [name],
  isnull(a.total,0) as [a.total],
  isnull(a.money,0) as [a.money],
  isnull(b.total,0) as [b.total],
  isnull(b.money,0) as [b.money]
from A
full join B on a.name=b.name

[百年树人]

---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([name] varchar(1),[total] int,[money] int)
insert [A]
select 'a',11,22 union all
select 'b',1,23
if object_id('[B]') is not null drop table [B]
go
create table [B]([name] varchar(1),[total] int,[money] int)
insert [B]
select 'a',4,6 union all
select 'c',17,9
 
---查询---
select 
  isnull(a.name,b.name) as [name],
  isnull(a.total,0) as [a.total],
  isnull(a.money,0) as [a.money],
  isnull(b.total,0) as [b.total],
  isnull(b.money,0) as [b.money]
from A
full join B on a.name=b.name
order by name 

---结果---
name a.total     a.money     b.total     b.money     
---- ----------- ----------- ----------- ----------- 
a    11          22          4           6
b    1           23          0           0
c    0           0           17          9

[Freeid_shi]

好了， 多谢大家