日期:2014-05-17 浏览次数:20452 次
----------------------------
-- 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 &nbs