MySQL - 如何创建一个新表，该表是两个现有表的主键的连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2112043/
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
MySQL - How to create a new table that is a join on primary key of two existing tables
提问by Richard H
I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table.
我有两个现有的表，具有不同的字段，除了主要 ID（a varchar，而不是 an int）。我想创建第三个表，它本质上是这两个表的合并，这样对于给定的主键，我将所有字段都放在一个表中。
How can this be done?
如何才能做到这一点？
采纳答案by Bandi-T
If you are sure you have one and exactly one row in both tables for a given primary ID, then this should work:
如果您确定在给定的主 ID 的两个表中都只有一行，那么这应该有效：
SELECT
tablea.field1, tablea.field2, tablea.field3, ... tablea.fieldn, <---- field list
tableb.field1, tableb.field2, tableb.field3, ... tableb.fieldm <---- field list
FROM
tablea, tableb
WHERE
tablea.primaryID = tableb.primaryID
You might want to omit tablea's and tableb's primary ID field from the field list if you do not actually need them (in this query both will contain the same value due to the tablea.primaryID = tableb.primaryIDcondition).
如果您实际上并不需要 tablea 和 tableb 的主 ID 字段，您可能希望从字段列表中省略它们（在此查询中，由于tablea.primaryID = tableb.primaryID条件的原因，它们将包含相同的值）。
The syntax is relatively similar for a VIEWas well.
a 的语法也相对相似VIEW。
回答by Max Shawabkeh
CREATE TABLE result AS
(SELECT first.*,
second.f1,
second.f2,
second.f3
FROM first
INNER JOIN second
ON first.id = second.id);
To get a view, do the same except replace "TABLE" with "VIEW". If you go with the table rather than the view, make sure to add a primary key as that will not be added by default.
要获得视图，请执行相同的操作，只是将“TABLE”替换为“VIEW”。如果您使用表而不是视图，请确保添加主键，因为默认情况下不会添加主键。
回答by Dhrumil Bhankhar
For MS SQL use this
对于 MS SQL 使用这个
SELECT * INTO result
FROM table1
INNER JOIN table2
ON table1.id = table2.id
回答by TMN
Why are you creating a new table? Why don't you just execute a query whenever you need the data? If you're just joining two tables on their primary key, then the majority of your data access time is going to be spent marshalling the data back to your application. You're not going to be saving much time pre-joining the tables, and you'll be eating a lot of space. Plus, you're taking aim at your big toe, just waiting for the first time you update your source tables and forget to run your update script to copy the changes to your joined table. Duplicate data is evil, but sometimes it's necessary. This doesn't sound like one of those times.
你为什么要创建一个新表？为什么不在需要数据时执行查询？如果您只是在主键上连接两个表，那么大部分数据访问时间将用于将数据编组回您的应用程序。你不会在加入桌子之前节省很多时间，而且你会吃很多空间。另外，您正在瞄准大脚趾，只是在等待第一次更新源表时忘记运行更新脚本以将更改复制到连接表中。重复数据是邪恶的，但有时它是必要的。这听起来不像那些时代之一。
