求一连接的SQL语句

[fionazou]

有表a和表b
a表的数据如下:
C1 C2
2 100
4 200
8 300
b表的数据如下
C1 C2
2 500
4 600
A 700
希望得到的结果如下:
C C1 C2
2 100 500
4 200 600
8 300 0
A 0 700
希望一个语句可以写好.谢谢了.

[jwdream2008]

Up!

[水族杰纶]

select isnull(a.c1,b.c1)c,
       isnull(sum(a.c2),0)c1,
       isnull(sum(b.c2),0)c2
from a full join b on a.c1=b.c1
group by  isnull(a.c1,b.c1)

[水族杰纶]

if object_id('[a]') is not null drop table [a]
go
create table [a] (C1 varchar(2),C2 int)
insert into [a]
select 2,100 union all
select 4,200 union all
select 8,300
if object_id('[b]') is not null drop table [b]
go
create table [b] (C1 nvarchar(2),C2 int)
insert into [b]
select '2',500 union all
select '4',600 union all
select 'A',700
select isnull(a.c1,b.c1)c,
       isnull(sum(a.c2),0)c1,
       isnull(sum(b.c2),0)c2
from a full join b on a.c1=b.c1
group by  isnull(a.c1,b.c1)
/*
c    c1          c2
---- ----------- -----------
2    100         500
4    200         600
8    300         0
A    0           700
警告: 彙總或其他 SET 作業已刪除 Null 值。

(4 個資料列受到影響)

*/

[dawugui]

select isnull(a.c1,b.c1) c,
       isnull(a.c2,0) c1,
       isnull(b.c2,0) c2
from a full join b on a.c1 = b.c1

[dawugui]

create table [a] (C1 varchar(2),C2 int)
insert into [a]
select 2,100 union all
select 4,200 union all
select 8,300
create table [b] (C1 nvarchar(2),C2 int)
insert into [b]
select '2',500 union all
select '4',600 union all
select 'A',700
go

select isnull(a.c1,b.c1) c,
       isnull(a.c2,0) c1,
       isnull(b.c2,0) c2
from a full join b on a.c1 = b.c1

drop table a , b

/*
c    c1          c2          
---- ----------- ----------- 
2    100         500
4    200         600
8    300         0
A    0           700

（所影响的行数为 4 行）


*/

[ACMAIN_CHM]

select C,max(CA) as C1, max(CB) as C2
from (
select C1 as C, C2 as CA,0 as CB from 表a
union all
select C1 as C,0, C2 from 表b
) t
group by C

[--小F--]

select
   isnull(a.c1,b.c1) c,
   isnull(a.c2,0) c1,
   isnull(b.c2,0) c2
from
   a full join b 
on
   a.c1 = b.c1