SQL 查看包含相同列的多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/352569/
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
View over multiple tables containing same columns
提问by nearly_lunchtime
I have four tables containing exactly the same columns, and want to create a view over all four so I can query them together.
我有四个包含完全相同列的表，并且想要创建所有四个表的视图，以便我可以一起查询它们。
Is this possible?
这可能吗？
(for tedious reasons I cannot/am not permitted to combine them, which would make this irrelevant!)
（出于乏味的原因，我不能/不允许将它们组合在一起，这会使这变得无关紧要！）
回答by AlfaZulu
Assuming that in addition to having the same column names, columns of the same contain the same data, you want to create a view that is the union of all those tables.
假设除了具有相同的列名外，相同的列包含相同的数据，您希望创建一个视图，该视图是所有这些表的联合。
Something like the following should work, but my SQL is rusty:
像下面这样的东西应该可以工作，但我的 SQL 生锈了：
(CREATE VIEW view_name AS
(SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3));
回答by Jonathan
It may be worth noting that you might need to use "union all" to preserve unique rows which may exist in more than one of the tables. A standard union will remove duplicates.
值得注意的是，您可能需要使用“union all”来保留可能存在于多个表中的唯一行。标准联合将删除重复项。
回答by Pete OHanlon
It is difficult to tell from your query whether you expect the data to be returned based on a UNION, or as a view containing the columns discretely. This obviously has an effect.
很难从您的查询中判断您是希望数据基于 UNION 返回，还是作为包含离散列的视图返回。这显然是有影响的。
Consider the following sample:
考虑以下示例：
TableA
ID Name RelatedID
1 John 2
2 Paul 1
TableB
ID Name RelatedID
1 Ringo 1
2 George 1
TableC
ID Name RelatedID
1 Bob 1
TableD
ID Name RelatedID
1 Kate NULL
Now, run the following query against this:
现在，针对此运行以下查询：
SELECT ID, Name FROM TableA UNION ALL SELECT ID, Name FROM TableB UNION ALL SELECT ID, Name FROM TableC UNION ALL SELECT ID, Name FROM TableD
SELECT ID, Name FROM TableA UNION ALL SELECT ID, Name FROM TableB UNION ALL SELECT ID, Name FROM TableC UNION ALL SELECT ID, Name FROM TableD
This results in the following output:
这导致以下输出：
1 John
2 Paul
1 Ringo
2 George
1 Bob
1 Kate
Is this what you are after? If so, you use a UNION query.
这是你追求的吗？如果是这样，则使用 UNION 查询。
Now, if the effect you are after is to have a discrete view of related data, you may need to do something like this:
现在，如果您想要获得相关数据的离散视图，您可能需要执行以下操作：
SELECT A.ID MasterID, A.Name MasterName,
B.ID BandID, B.Name BandName,
C.ID BlackadderID, C.Name BlackadderName
D.ID BlackadderRealID, D.Name BlackadderRealName
FROM
TableA A
INNER JOIN
TableB B
ON
A.RelatedID = B.ID
INNER JOIN
TableC C
ON
B.RelatedID = C.ID
INNER JOIN
TableD D
ON
C.RelatedID = D.ID
This will result in the following view of the data:
这将导致以下数据视图：
MasterID MasterName BandID BandName BlackAdderID BlackAdderName BlackadderRealID BlackadderRealName
1 John 2 George 1 Bob 1 Kate
2 Paul 1 Ringo 1 Bob 1 Kate
回答by RedFilter
Rather than UNION, use UNION ALL, unless you specifically want duplicate rows to be excluded. UNION on its own takes longer to execute (because of the sort it does to find dupes), and removes duplicate rows.
使用 UNION ALL 而不是 UNION，除非您特别希望排除重复的行。UNION 本身需要更长的时间来执行（因为它会进行查找重复项的排序），并删除重复的行。
回答by rix
Use union. Here is explanation
使用联合。 这是解释
回答by terjetyl
Use the union statement
使用联合声明
select * from table1
union
select * from table2
union
select * from table3
回答by Garry Shutler
You can if you union them (I would suggest including some indicator as to which table each record came from):
如果你联合它们，你可以（我建议包括一些关于每条记录来自哪个表的指标）：
select table1.column1, 1 as TableNumber
from table1
union
select table2.column1, 2 as TableNumber
from table2
.. etc ..
