SQL 两个表的数据合并成一个视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3267360/
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
Data from two tables into one view
提问by Marcin
Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table.
是否可以将两个表（具有相同字段）中的数据抓取到一个视图中。基本上，因此视图将数据视为一张表。
回答by OMG Ponies
Yes, using a UNION -
是的，使用 UNION -
CREATE VIEW vw_combined AS
SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2
...requires that there be the same number of columns, and the data types match at each position.
...要求列数相同，并且每个位置的数据类型匹配。
..preferrably, using a JOIN:
..最好是使用 JOIN：
CREATE VIEW vw_combined AS
SELECT *
FROM TABLE1 t1
JOIN TABLE2 t2 ON t2.col = t1.col
But I want to warn against depending on views - if not materialized, they are only prepared SQL statements. There's no performance benefit, and can negatively impact performance if you build a view based on another. Also, views are brittle - they can change, and you won't know until using a supporting view if there are issues.
但我想警告不要依赖视图 - 如果没有实现，它们只是准备好的 SQL 语句。没有性能优势，如果您基于另一个视图构建视图，可能会对性能产生负面影响。此外，视图是脆弱的——它们可以改变，如果有问题，你不会知道直到使用支持视图。
回答by Shannon Severance
create or replace view view_name as
select * from table_1
union all select * from table_2
Note: The columns in the view are set at the time the view is created. Adding columns to table_1 and table_2 after view creation will not show up in view_name. You will need to re-run the above DDL to get new columns to show up.
注意：视图中的列是在创建视图时设置的。创建视图后向 table_1 和 table_2 添加列将不会显示在 view_name 中。您需要重新运行上述 DDL 才能显示新列。
If you want duplicate rows to be collasped to single rows (but potentially more work for the server):
如果您希望将重复的行折叠为单行（但服务器可能需要做更多的工作）：
create or replace view view_name as
select * from table_1
union select * from table_2
Generally it is bad form to use *in the select list, but assuming that the queries using the view are going to choose just what they need, I would use it here instead of explicitily naming all the columns. (Especially since I wouldn't want to have to add the column names when table_1 and table_2 change.)
通常*在选择列表中使用它是不好的形式，但假设使用视图的查询将选择他们需要的内容，我会在这里使用它而不是显式命名所有列。（特别是因为我不想在 table_1 和 table_2 更改时添加列名。）
