如何合并列中略有不同的多个表 [英] How to combine multiple tables that vary slightly in columns

问题描述

我有多个表，其中大约有10个公共列，但是有些表有1-2个额外的列.

我想将所有这些表合并到一个表中，每个表的每一行都有一行，对于每个特定行的源表中不存在的任何列，请使用NULL值.

所以我的输入大致如下:

table1
id  |  colA  | colB

table2
id  |  colA  | colB  | colC

table3
id  |  colA  | colB  | colD

我正试图得到这个:

allTables
id  |  colA  | colB  | colC | colD

在上面的示例中，table1中的所有行在allTables中的colC和colD都将为NULL值，table2中的所有行的colD将具有空值，而col3中的table3的所有行将都具有null值.

一些注意事项:

• 表之间的列ID完全不同或无关
• 我的示例显示了3张桌子，但我大约有8-9张桌子.
• 每个源表中都存在重复的行，应保留这些行.

我特别想知道是否有与最高投票者类似的答案解决方案

SELECT
    id,
    colA,
    colB,
    NULL AS colC,
    NULL AS colD
FROM
    Table1
UNION ALL
SELECT
    id,
    colA,
    colB,
    colC,
    NULL AS colD
FROM
    Table2
UNION ALL
SELECT
    id,
    colA,
    colB,
    NULL AS colC,
    colD
FROM
    Table3

由于ID无关，因此您可能还想跟踪该行来自哪个表，以防表之间存在重复.为此，只需在三个SELECT语句中的每个语句中都使用一个硬编码值和一个别名，而别名则具有不同的值.

I have multiple tables where there are roughly 10 common columns, but some tables have 1-2 extra columns.

I would like to combine all these tables into one table with a row for each row from each table, with NULL values for any columns that didn't exist in each particular row's source table.

So my inputs look roughly like this:

table1
id  |  colA  | colB

table2
id  |  colA  | colB  | colC

table3
id  |  colA  | colB  | colD

And I am trying to get this:

allTables
id  |  colA  | colB  | colC | colD

In the above example all rows from table1 would have NULL values for colC and colD in allTables, all rows from table2 would have null values for colD, and all rows from table3 would have null values in colC.

A couple notes:

• The column id is not the same or related between tables in any way
• My example shows 3 tables, but I have about 8-9.
• Duplicate rows exist within each source table and should be preserved.

In particular I'm interested if there's an answer similar to the top voted one here or something like it that's more generalized.

解决方案

SELECT
    id,
    colA,
    colB,
    NULL AS colC,
    NULL AS colD
FROM
    Table1
UNION ALL
SELECT
    id,
    colA,
    colB,
    colC,
    NULL AS colD
FROM
    Table2
UNION ALL
SELECT
    id,
    colA,
    colB,
    NULL AS colC,
    colD
FROM
    Table3

Since the ids are not related, you might also want to track which table the row came from in case there are duplicates between the tables. To do that, just have a hard-coded value with an alias with a different value in each of the three SELECT statements.

这篇关于如何合并列中略有不同的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！