postgresql 如何合并2个完全不同的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9113466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to merge 2 completely different tables?
提问by user1183513
Possible Duplicate:
Combine two tables that have no common fields
可能的重复：
合并两个没有共同字段的表
I'm looking at merging 2 tables, that have nothing in common (No columns, No IDs) into 1 single table. it's for reporting purposes. I need to just grab all the columns in table 1 and all the columns in table 2 and put them into 1 table, so the data doesn't change, it's just that instead of being in 2 tables they'll be into 1.
我正在考虑将 2 个没有任何共同点（无列、无 ID）的表合并到 1 个单表中。它用于报告目的。我只需要抓取表 1 中的所有列和表 2 中的所有列并将它们放入 1 个表中，这样数据就不会改变，只是它们不是在 2 个表中，而是在 1 个中。
Example
例子
Table1: KeyboardID, Computermodel, factory
表格1： KeyboardID, Computermodel, factory
Table2: MouseID, Speakers, Circuitboard
表2： MouseID, Speakers, Circuitboard
New table of those 2 combined: KeyboardID, Computermodel, factory, MouseID, Speakers, Circuitboard
这两个组合的新表： KeyboardID, Computermodel, factory, MouseID, Speakers, Circuitboard
Update: I'm gonna clarify myself:
更新：我要澄清自己：
Here's my situation View1: StarID, Planet, Sunand then View2: StarID, Planet, SunAs you can see they have the exact same column names; Here's how I'm expecting my 3rd view to look like:
这是我的情况 View1：StarID, Planet, Sun然后View2: StarID, Planet, Sun正如您所见，它们具有完全相同的列名；这是我期望我的第三个视图的样子：
StarID, Planet, Planet2, Sun, Sun2
StarID, Planet, Planet2, Sun, Sun2
I would not mind having the 3rd view look like this:
我不介意让第三个视图看起来像这样：
StarID, StarID2, Planet, Planet2, Sun, Sun2
StarID, StarID2, Planet, Planet2, Sun, Sun2
here's the catch, they do not relate at all, you're probably thinking "Join them on StarID" unfortunately I cant as technically I need StarIDfrom View1 to be appended to StarIDon View2 or have them in completely separate columns (this is because there's no relationship in the data) to complicate things, StarIDmay not be unique (I'm not sure if there's uniqueness constraints to views I'm fairly new to postgres)
这就是问题所在，它们根本不相关，您可能在想“加入他们StarID”不幸的是我不能像技术上那样将StarIDView1 附加到StarIDView2 或将它们放在完全独立的列中（这是因为没有关系在数据中）使事情复杂化，StarID可能不是唯一的（我不确定视图是否存在唯一性约束，我对 postgres 还很陌生）
回答by Matt Fenwick
You can use a full outer join:
您可以使用完整的外连接：
FULL OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.
全外连接
首先，执行内连接。然后，对于 T1 中不满足与 T2 中任何行的连接条件的每一行，在 T2 的列中添加一个带有空值的连接行。此外，对于不满足与 T1 中任何行的连接条件的 T2 的每一行，添加一个在 T1 的列中具有空值的连接行。
So your query would be something like:
因此，您的查询将类似于：
select
* -- or explicitly list column names
from
<table1>
full outer join
<table2> on <some join condition>
Note that the join condition should always be false. The result will be something like:
请注意，连接条件应始终为假。结果将类似于：
KeyboardID Computermodel factory MouseID Speakers Circuitboard
1 something something3 (null) (null) (null)
2 something2 something4 (null) (null) (null)
(null) (null) (null) 17 something5 something8
(null) (null) (null) 22 something6 something7
Notice that the columns from the "other" table are always null, and that the number of rows is (number of rows in Table1) + (number of rows in Table2).
请注意，“其他”表中的列始终为空，并且行数为（表 1 中的行数）+（表 2 中的行数）。
Note: I'm not a PostGresQL expert, so I didn't have a chance to test this query.
注意：我不是 PostGresQL 专家，所以我没有机会测试这个查询。
回答by egrunin
It sounds as if you're trying to hack together a two-up listing, where the two sides don't actually have anything to do with each other.
听起来好像您试图将两个列表拼凑在一起，其中双方实际上没有任何关系。
You can synthesize a key by using row_number(). I don't have Postgres here, but something like this might work:
您可以使用 row_number() 合成一个键。我这里没有 Postgres，但像这样的东西可能会奏效：
SELECT * FROM
(SELECT KeyboardID, Computermodel, factory, row_number() as num FROM Table1) AS X
FULL OUTER JOIN
(SELECT MouseID, Speakers, Circuitboard, row_number() as num FROM Table2) AS Y
ON x.num = y.num
It arbitrarily matches rows from the two tables, with no duplicates.
它任意匹配两个表中的行，没有重复。
By creating a synthetic 'key', you don't get all those "(null)" values.
通过创建合成“密钥”，您不会获得所有这些“（空）”值。
回答by Brian
Take a look at this answer. paxdiablo gave probably the clearest and most complete explanation of your options for accomplishing this.
看看这个答案。paxdiablo 可能对实现此目的的选项给出了最清晰、最完整的解释。
回答by Kalim Shaikh
INSERT INTO table3 (KeyboardID, Computermodel, factory) SELECT * FROM table1;
INSERT INTO table3 (KeyboardID, Computermodel, factory) SELECT * FROM table1;
INSERT INTO table3 (MouseID, Speakers, Circuitboard) SELECT * FROM table2;
INSERT INTO table3 (MouseID, Speakers, Circuitboard) SELECT * FROM table2;
You can insert data sing these commands, you need to create table dynamically as well?
你可以用这些命令插入数据，你也需要动态创建表吗？
