求SQL语句

[ming_Y]

有两个表:
表1
key field1 field2 customer_name
1 100 50 A
2 90 20 B
3 70 25 C
4 65 80 D
5 120 75 C

表2

rkey field3 customer_name
1 41 A
2 15 B
8 10 C
7 100 D
9 60 B
表3
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

将两个表的内容合并为表3,求SQL语句

[ming_Y]

第二个表rkey错了,为key,和其它两个一样的.

[htl258_Tony]

---------------------------------
--  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 行受影响)
*/

[--小F--]

[Quote=引用 2 楼 htl258 的回复:]
SQL code---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 17:27:53
---------------------------------
--> 生成测试数据表:t1Ifnotobject_id('[t1]')isnullDroptable[t1]GoCreatetabl¡­
[/Quote]

...
速度真快

[izbox]

select key1,Sum(Field1),Sum(Field2),Sum(Field3),customer_name from (select key1,Field1,Field2,0 as Field3,customer_name from tb1 
union all
select key1,0 as Field1,0 as Field2,Field3,customer_name from tb2) a
group by key1,customer_name

[izbox]

select key1,Sum(Field1),Sum(Field2),Sum(Field3),customer_name from 
(
select key1,Field1,Field2,0 as Field3,customer_name from tb1 
union all
select key1,0 as Field1,0 as Field2,Field3,customer_name from tb2)
a
group by key1,customer_name

---------结果------------
1	100	50	41	a
2	90	20	15	b
9	0	0	60	b
3	70	25	0	c
5	120	75	0	c
8	0	0	10	c
4	65	80	0	d
7	0	0	100	d

[izbox]

full join 没用过，学习下！