sql - 如何将两个表(具有相同架构)组合成一个具有不同值的表并指示每行来自哪个表？

Question

假设我在 SQL 中有两个表:

Table_Alpha   Table_Bravo
Id | Name     Id | Name
---+------    ---+-----
 1 | Alice     1 | Charlie
 2 | Bob       2 | Bob
 3 | Charlie   3 | Dorothy

我想将两个表组合成一个表，避免重复并跟踪每个名称来自哪个表，如下所示:

Result
Name   | Alpha | Bravo
-------+-------+------
Alice  |   1   |   0  
Bob    |   1   |   1  
Charlie|   1   |   1  
Dorothy|   0   |   1  

我认为我想要的查询看起来像这样:

SELECT Name, 1 AS Alpha, 0 AS Bravo FROM Table_Alpha
UNION
SELECT Name, 0 AS Alpha, 1 AS Bravo FROM Table_Bravo;

但是，上述查询将为出现在两个表中的每个名称返回两行。如何编写查询，以便它为每个不同的名称返回一行？

Answer 1

这会起作用吗？

SELECT DISTINCT Name, sum(Alpha) as 'Alpha', sum(Bravo) as 'Bravo'
FROM (
    SELECT Name, 1 as Alpha, 0 as Bravo from Table_Alpha
    UNION
    SELECT Name, 0 as Alpha, 1 as Bravo from Table_Bravo
) X
GROUP BY Name