求SQL语句

Question

 有两个表:
        表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语句                 

Answer 1

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

Answer 2

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-24 17:27:53
---------------------------------
--> 生成测试数据表:t1If 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--> 生成测试数据表:t2If 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 行受影响)
*/

Answer 3

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

Answer 4

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

Answer 5

full join 没用过，学习下！