全外连接

Question

我有以下情况(表模式：ID, first name, last name, value)。

table 1

ID | first name  | last name  | value
--------------------------------------
1  |    John     | Goodman    |  5
2  |    Peter    | Snow       |  6
3  |    Mike     | Walker     |  7
4  |    John     | Goodman    |  8

table 2

ID | first name  | last name  | value
--------------------------------------
1  |  Peter      | Snow       | 2
2  |  Bobby      | White      | 1
3  |  Mike       | Walker     | 1
4  |  Brad       | West       | 2
5  |  Peter      | Snow       | 3

我想写完整的外部联接，以便在第4列中得到sum，但是每个名称应该只放在一个结果(连接)表中一次，如下所示：

成果表：

ID | first name  | last name  | value.table1 | value.table2 
-----------------------------------------------------------
1  |  John       | Goodman    | 5            | 0
2  |  Peter      | Snow       | 6            | 5
3  |  Mike       | Walker     | 7            | 1
4  |  Bobby      | White      | 0            | 1
5  |  Brad       | West       | 0            | 2

我怎样才能做到这一点？

Answer 1

像这样的东西应该可以工作(如果我暂时忽略ID列)：

SELECT
    COALESCE(t1.FirstName,t2.FirstName) as FirstName,
    COALESCE(t1.LastName,t2.LastName) as LastName,
    COALESCE(t1.value,0) as t1value,
    COALESCE(t2.value,0) as t2value
FROM
    (select FirstName,LastName,SUM(value) as value
     from table1
     group by FirstName,LastName) t1
        full outer join
    (select FirstName,LastName,SUM(value) as value
     from table2
     group by FirstName,LastName) t2
        on
            t1.FirstName= t2.FirstName and
            t1.LastName=  t2.LastName

Answer 2

With s as (Select firstname, lastname , value
From table1
Union all
Select firstname, lastname , value
From table2)
Select firstname, lastname , sum(value)
From s
Group by firstname , lastname

Answer 3

试试这个：

SELECT COALESCE(T1.FName,T2.FName) as FName,COALESCE(T1.LName,T2.LName) as LName,ISNULL(T1.Value,0) as Value1,ISNULL(T2.Value,0) as Value2 
FROM Table1 T1 FULL OUTER JOIN
     Table2 T2 ON T1.FName=T2.FName AND T1.LName=T2.LName

解释：

COALESCE返回列表中的第一个，而不是NULL。

ISNULL用于将NULL值替换为0。结果：

FNAME   LNAME   VALUE1  VALUE2
John    Goodman 5       0
Peter   Snow    6       2
Peter   Snow    6       3
Mike    Walker  7       1
John    Goodman 8       0
Bobby   White   0       1
Brad    West    0       2

见SQL Fiddle中的结果。