---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 17:27:53
---------------------------------
--> 生成测试数据表:t1
If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([key] int,[field1] int,[field2] int,[customer_name] nvarchar(1))
Insert t1
Select 1,100,50,'A' union all
Select 2,90,20,'B' union all
Select 3,70,25,'C' union all
Select 4,65,80,'D' union all
Select 5,120,75,'C'
Go
--Select * from t1
--> 生成测试数据表:t2
If not object_id('[t2]') is null
Drop table [t2]
Go
Create table [t2]([rkey] int,[field3] int,[customer_name] nvarchar(1))
Insert t2
Select 1,41,'A' union all
Select 2,15,'B' union all
Select 8,10,'C' union all
Select 7,100,'D' union all
Select 9,60,'B'
Go
--Select * from t2
-->SQL查询如下:
select isnull(a.[key],b.[rkey]) as [key],isnull([field1],0) [field1],isnull([field2],0) [field2],
isnull(b.[field3],0) [field3],isnull(a.[customer_name],b.[customer_name]) [customer_name]
from t1 a
full join t2 b
on a.[key]=b.rkey
/*
key field1 field2 field3 customer_name
----------- ----------- ----------- ----------- -------------
1 100 50 41 A
2 90 20 15 B
3 70 25 0 C
4 65 80 0 D
5 120 75 0 C
8 0 0 10 C
7 0 0 100 D
9 0 0 60 B
(8 行受影响)
*/
