sql - 全外连接

Question

我有以下情况(表架构:ID，名字，姓氏，值)。:

表 1:

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

表 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 列的总和，但每个名称在结果(连接)表中应该只放置一次，如下所示:

结果表:

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