当前位置：文江博客话题详情

将两个表中的数据放入一个视图中

发布于 2024-09-10 16:17:56 字数 50 浏览 9 评论 0原文

是否可以将两个表（具有相同字段）的数据抓取到一个视图中。基本上，视图将数据视为一张表。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情释 2024-09-17 16:17:56

是的，使用 UNION -

CREATE VIEW vw_combined AS
   SELECT * FROM TABLE1
   UNION ALL
   SELECT * FROM TABLE2

...要求列数相同，并且每个位置的数据类型都匹配。

..最好使用 JOIN：

CREATE VIEW vw_combined AS
   SELECT * 
    FROM TABLE1 t1
    JOIN TABLE2 t2 ON t2.col = t1.col

但我想警告不要依赖视图 - 如果没有具体化，它们只是准备好的 SQL 语句。没有任何性能优势，并且如果您基于另一个视图构建视图，可能会对性能产生负面影响。此外，视图很脆弱 - 它们可能会发生变化，并且在使用支持视图之前您不会知道是否存在问题。

Yes, using a 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:

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.

回复收藏 0 原文

一身软味 2024-09-17 16:17:56

create or replace view view_name as
select * from table_1
union all select * from table_2

笔记：
视图中的列是在创建视图时设置的。创建视图后向 table_1 和 table_2 添加列不会显示在 view_name 中。您将需要重新运行上述 DDL 才能显示新列。

如果您希望将重复行折叠为单行（但可能会给服务器带来更多工作）：

create or replace view view_name as
select * from table_1
union select * from table_2

通常在选择列表中使用 * 是不好的形式，但假设使用视图的查询是要选择他们需要的内容，我会在这里使用它，而不是明确命名所有列。（特别是因为我不想在 table_1 和 table_2 更改时添加列名。）

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.

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.)

回复收藏 0 原文

~没有更多了~