SQL 从另一个视图创建视图

Creating View from Another View

提问by Hymanson Bray

I haven't found anything online or in any other resource after searching, and I was wondering if you could form a View by joining another view and some other tables? Something similar to this I guess with Server_ref.part_notification_view being the view getting joined.

搜索后我没有在网上或任何其他资源中找到任何内容，我想知道您是否可以通过加入另一个视图和其他一些表来形成一个视图？我猜想与 Server_ref.part_notification_view 是加入的视图类似的东西。

Create View "PART_NOTIFICATION_VIEW" ("NOTIFICATION_IX", "PART_NBR", "MFG_CD", "PART_CLASS_CD", "LEADTIME", "BILLTO_CUST_NBR", "BILL_TO_ACCT_NM", "CUST_PART_NBR", "LAST_CUST_PO", "LAST_REQ_DT", "QTY_OPEN", "YEAR_USAGE", "AVAILABLE_SALE_STANDARD_QT", "ISSUE_DATE", "EFFECTIVE_DATE", "BRIEF_DESCRIPTION", "NOTIFICATION_TYPE", "ACTUAL_DOCUMENT_LINK", "AFFECTED_PARTS_LIST_DOC_LINK", "EMAIL_LINK", "FILE_FOLDER", "RECOMMENDED_REPLACEMENT", "PCN_TYPE", "IMPACT", "MANUFACTURER_NM", "LAST_BUY_DT", "LAST_SHIP_DT", "SALES_MIN_BUY_QTY", "SALES_MIN_PKG_QTY", "PART_DESC", "BOND_QOH", "BOND_QIT", "BRANCH_QOH", "BRANCH_QIT", "BOND_QTY", "BOND_PIPELINE", "BOND_OP", "BRAND_CD", "STATUS", "COMMENTS")
AS
  SELECT
    svr.notification_ix,
    svr.part_nbr,
    svr.mfg_cd,
    svr.part_class_cd,
    svr.leadtime,
    svr.billto_cust_nbr,
    svr.bill_to_acct_nm,
    svr.cust_part_nbr,
    svr.last_cust_po,
    svr.last_req_dt,
    svr.qty_open,
    svr.year_usage,
    svr.available_sale_standard_qt,
    svr.issue_date,
    svr.effective_date,
    svr.brief_description,
    svr.notification_type,
    svr.actual_document_link,
    svr.affected_parts_list_doc_link,
    svr.email_link,
    svr.file_folder,
    svr.recommended_replacement,
    svr.pcn_type,
    svr.impact,
    svr.manufacturer_nm,
    svr.last_buy_dt,
    svr.last_ship_dt,
    svr.sales_min_buy_qty,
    svr.sales_min_pkg_qty,
    svr.part_desc,
    NVL(svr.bond_qoh, 0)                                                                    AS bond_qoh,
    NVL(svr.bond_qit, 0)                                                                    AS bond_qit,
    NVL(svr.branch_qoh, 0)                                                                  AS branch_qoh,
    NVL(svr.branch_qit, 0)                                                                  AS branch_qit,
    NVL(svr.bond_qoh, 0)      + NVL(svr.bond_qit, 0) + NVL(svr.branch_qoh, 0) + NVL(svr.branch_qit, 0) AS bond_qty,
    NVL(svr.bond_pipeline, 0) + NVL(svr.po_qt, 0)                                                 AS bond_pipeline,
    svr.bond_op,
    svr.brand_cd,
    cs.status,
    cc.comments
  FROM part_notification_view svr
  JOIN css_status cs
  ON svr.part_nbr = cs.part_nbr
  AND svr.mfg_cd = cs.mfg_cd
  AND svr.billto_cust_nbr = cs.account
  JOIN css_comment cc
  ON svr.part_nbr = cc.part_nbr
  AND svr.mfg_cd = cc.mfg_cd
  AND svr.billto_cust_nbr = cc.account;

回答by Alex Poole

You can certainly have a view that's built on top of another view:

你当然可以有一个建立在另一个视图之上的视图：

create table my_table (id number, name varchar2(20), address varchar2(30));

table MY_TABLE created.

create or replace view my_view_1 as
select id, name
from my_table;

view MY_VIEW_1 created.

create or replace view my_view_2 as
select mv1.id, mv1.name, mt.address
from my_view_1 mv1
join my_table mt on mt.id = mv1.id;

view MY_VIEW_2 created.

But you can't reference anything in the underlying tables, including any fields that are not part of the view:

但是您不能引用基础表中的任何内容，包括不属于视图的任何字段：

create or replace view my_view_3 as
select mv1.id, mv1.name, mv1.address
from my_view_1 mv1
join my_table mt on mt.id = mv1.id;

SQL Error: ORA-00904: "MV1"."ADDRESS": invalid identifier
00904. 00000 -  "%s: invalid identifier"

The underlying tables having the same columns isn't an issue, and if you include more than on in the view then you'd have to alias them anyway.

具有相同列的基础表不是问题，如果您在视图中包含多个，那么无论如何您都必须为它们设置别名。

You can sometimes get performance issues doing this, and it might be faster and more reliable - though possibly harder to maintain - if you create your new view against the same base tables, and expand it to include the extra data you want.

这样做有时会遇到性能问题，如果您针对相同的基表创建新视图，并扩展它以包含您想要的额外数据，它可能会更快、更可靠 - 尽管可能更难维护。

回答by David Aldridge

Long answer ...

长答案...

It's one of the fundamental characteristics of a relational database that there should be no logical difference between tables, queries results, and views (which are simply stored queries). In fact the "relational" refers to the rows of data that are accessible through any one of these. http://en.wikipedia.org/wiki/Relation_%28database%29

表、查询结果和视图（它们只是存储的查询）之间不应存在逻辑差异，这是关系数据库的基本特征之一。事实上，“关系”是指可通过其中任何一个访问的数据行。http://en.wikipedia.org/wiki/Relation_%28database%29

There are of course differences imposed to different degrees by different RDBMSs, particularly when it comes to DDL commands (update, delete, insert), and they all impose restrictions on the kinds of object that DDL can be applied to.

当然，不同的 RDBMS 会在不同程度上强加差异，尤其是在涉及 DDL 命令（更新、删除、插入）时，它们都对 DDL 可以应用于的对象类型施加了限制。

Taking Oracle as an example, the system will allow updates and deletes on key-preserved views, and inserts are possible but rarely used in practice (an "instead of" trigger type is available to allow DDL against any view).

以 Oracle 为例，系统将允许对保留键的视图进行更新和删除，插入是可能的，但在实践中很少使用（“替代”触发器类型可用于允许针对任何视图进行 DDL）。

So given all that, you can run a select against:

因此，鉴于所有这些，您可以针对以下对象运行选择：

1. A table
2. A set of joined tables
3. A view
4. A query (commonly referred to as an in-line view)
5. A query joined to a view and a table
6. etc

1. 一张桌子
2. 一组连接表
3. 一个看法
4. 查询（通常称为内嵌视图）
5. 连接到视图和表的查询
6. 等等

... and that select can therefore be encapsulated in a view definition.

...因此该选择可以封装在视图定义中。

Short answer: Yes

简短回答：是的