结果集合并，想了一天没整出来。
表合并 sql?server 数据库 join
[解决办法]
select t.cc,t.s,isnull(A.a,0)as A_Amout,isnull(B.a,0)as B_Amout,isnull(C.a,0)as C_Amout
(select cc,s from A union select cc,s from B union select cc,s from C )t
left join A on a.cc=t.cc and a.s=t.s
left join B on b.cc=t.cc and b.s=t.s
left join C on c.cc=t.cc and c.s=t.s
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-07 17:25:35
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([cc] varchar(2),[s] varchar(1),[a] numeric(4,1))
insert [A]
select 'C1','a',235.4 union all
select 'C1','b',88 union all
select 'C1','c',34
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([cc] varchar(2),[s] varchar(1),[a] int)
insert [B]
select 'C1','a',11 union all
select 'C1','b',22 union all
select 'C1','d',33
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([cc] varchar(2),[s] varchar(1),[a] int)
insert [C]
select 'C1','a',2000 union all
select 'C1','b',500
--------------开始查询--------------------------
SELECT COALESCE(a.cc,b.cc,c.cc) AS cc,COALESCE(a.s,b.s,c.s) AS s,ISNULL(a.a,0),ISNULL(b.a,0),ISNULL(c.a,0)
FROM a full JOIN b ON a.cc=b.cc AND a.s=b.s
full JOIN c ON a.cc=c.cc AND a.s=c.s
----------------结果----------------------------
/*
cc s
---- ---- --------------------------------------- ----------- -----------
C1 a 235.4 11 2000
C1 b 88.0 22 500
C1 c 34.0 0 0
C1 d 0.0 33 0
*/
