带m列的n个表的SQLite联合 [英] SQLite Union of n tables with m columns

问题描述

我有这个问题.动态创建了n个表，每个表有m列，这些列可以重复.该表共有2列，但它们之间没有相关数据，例如: 表格1 | A | B | Col1 | Col2 |

I have this problem. There are n tables created dynamically and each table has m columns, the columns could be repeated. This tables have in common 2 columns but there is no related data between them, for example: Table1 | A | B | Col1 | Col2 |

    Table2

| A | B | Col3 | Col4 |

| A | B | Col3 | Col4 |

    Table3

| A | B | Col1 | Col2 | Col4 |

| A | B | Col1 | Col2 | Col4 |

我要做的是将所有表合并成一个大表，如下所示:

What I want to do is to merge all the tables into a big one like this:

    BigTable

| A | B | Col1 | Col2 | Col3 | Col4 |

| A | B | Col1 | Col2 | Col3 | Col4 |

所有行并置，例如，如果table1中的行= 5，table2中的行= 3，table3中的行= 2，则大表将有10个条目.

And all the rows concatenated, for example if in table1 rows = 5, table2 rows = 3, table3 rows = 2, the big table will have 10 entries.

我可以通过使用如下查询来完成此操作:

I can accomplish this by using a query like this:

SELECT A, B, Col1, Col2, null as Col3, null as Col4 FROM Table1
UNION
SELECT A, B, null as Col1, null as Col2, Col3, Col4 FROM Table2
UNION
SELECT A, B, Col1, Col2, null as Col3, Col4 FROM Table3

但是我想知道是否有更好的方法来执行此操作，因为将有更多的列和更多的表，而且所有列都有可能是不同的.

But I want to know if there is a better way to do this, because there will be more columns and more tables, and there is the possibility that all the columns are different.

推荐答案

对查询的唯一改进是使用union all而不是union.仅在明确要删除重复项时才使用union，因为它总是尝试:

The only improvement to your query is to use union all instead of union. Only use union if you explicitly want to remove duplicates, because it always attempts to:

SELECT A, B, Col1, Col2, null as Col3, null as Col4 FROM Table1
UNION ALL
SELECT A, B, null as Col1, null as Col2, Col3, Col4 FROM Table2
UNION ALL
SELECT A, B, Col1, Col2, null as Col3, Col4 FROM Table3;

您可以进一步简化为:

SELECT A, B, Col1, Col2, null as Col3, null as Col4 FROM Table1
UNION ALL
SELECT A, B, null, null, Col3, Col4 FROM Table2
UNION ALL
SELECT A, B, Col1, Col2, null, Col4 FROM Table3;

列名仅用于union all中的第一个select.之后，按位置标识列.

The column names are only used for the first select in the union all. After that, the columns are identified by position.

编辑II:

有一个技巧可以用来在union all上获得逻辑"匹配.我不是特别喜欢它，但是您不必列出所有子查询的列.但是，select更为复杂，它还有另一个子查询，您仍然需要子查询:

There is a trick that you can use to get "logical" matches on union all. I don't particularly like it, but you don't have to list the columns for all the subqueries. However, the select is more complicated, and it has another subquery, and you still need subqueries:

select coalesce(t1.A, t2.A, t3.A) as A,
       coalesce(t1.B, t2.B, t3.B) as B,
       coalesce(t1.Col1, t2.Col1, t3.Col1) as col1,
       coalesce(t1.Col2, t2.Col2, t3.Col2) as col2,
       coalesce(t1.Col3, t2.Col3, t3.Col3) as col3
from (select 'Table1' as tablename union all
      select 'Table2' union all
      select 'Table3'
     ) driver left outer join
     (select t.*, 'Table1' as tablename
      from Table1
     ) t1
     on t1.tablename = driver.tablename left outer join
     (select t.*, 'Table2' as tablename
      from Table2
     ) t2
     on t2.tablename = driver.tablename left outer join
     (select t.*, 'Table3' as tablename
      from Table3
     ) t3
     on t3.tablename = driver.tablename;

这篇关于带m列的n个表的SQLite联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！