如何在合并两列时联合/连接访问“堆栈”2表

Question

除了两个公共字段外，我还有两个表，它们之间的行不相关。我试图加入他们这样的方式，有两个字段合并。

例如：

表A：

NameA    ID-A    SomefieldA    SomefieldB 
John     101     A-1           B-2
John     200     A-1           B-10
Smith    101     A-10          B-2

表B：

NameB    ID-B    SomefieldC    SomefieldD
John     101     C-1           D-2
David    2000    C-100         A-10
George   120     C-2           D-20

我想把这些桌子连在一起，这样会是这样的：

表C：

Name    ID    Somefield A    SomefieldB    SomefieldC    SomefieldD
John    101   A-1            B-2           (null)         (null)
John    200   A-1            B-10          (null)         (null)
Smith   101   A-10           B-2           (null)         (null)
John    101   (null)         (null)         C-1           D-2
David   2000  (null)         (null)         C-100         A-10
George  120   (null)         (null)         C-2           D-20

Answer 1

这只是一个简单的联合查询。您需要在两个表中提供所有字段。

SELECT NameA, [ID-A] As ID, SomefieldA, SomefieldB, Null As SomefieldC, Null As SomefieldD
FROM TableA
UNION ALL
SELECT NameB, [ID-B], Null As SomefieldA, Null As SomefieldB, SomefieldC, SomefieldD
FROM TableB

如果要对结果执行左联接，则必须使用子查询，它将如下所示：

SELECT *
FROM (    
    SELECT NameA, [ID-A] As ID, SomefieldA, SomefieldB, Null As SomefieldC, Null As SomefieldD
    FROM TableA
    UNION ALL
    SELECT NameB, [ID-B], Null As SomefieldA, Null As SomefieldB, SomefieldC, SomefieldD
    FROM TableB
) AS UQuery
LEFT JOIN TableC ON TableC.ID = UQuery.ID

Answer 2

我看不出有什么东西在合并。嗯，这应该可以：

select name, id, field_a, field_b, null as field_c, null as field_d from a
union all
select name, id, null as field_a, null as field_b, field_c, field_d from b;