gpt4 book ai didi

sql - 从另一个 View 创建 View

转载 作者:行者123 更新时间:2023-12-04 14:39:22 34 4
gpt4 key购买 nike

搜索后我没有在网上或任何其他资源中找到任何内容,我想知道您是否可以通过加入另一个 View 和其他一些表来形成一个 View ?我猜想与 Server_ref.part_notification_view 是加入的 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;

最佳答案

你当然可以有一个建立在另一个 View 之上的 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.

但是您不能引用基础表中的任何内容,包括不属于 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"

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

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

关于sql - 从另一个 View 创建 View ,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14834113/

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com