問題描述

我有下表:

• 我想創建視圖，以便對于 descr = 'O' 和兩個表中的公共 id_isin 字段值，檢查 ratio字段并僅取 ratio 字段值較低的行.
• 對于 descr = 'O' 并且如果 id_isin 存在于一個表中但不在另一個表中，則取那些行(雙向)
• 對于 descr != 'O'，從表 IS_ID_TST 中取出所有這些行.

• I want to create View such that for descr = 'O' and for common id_isin field value from both tables, check the ratio field and take only the row where ratio field value is low.
• for descr = 'O' and if the id_isin exist in one table but not in another then take those rows(bidirectional)
• For all the rows where descr ! = 'O', take all those rows from table IS_ID_TST.

以下是視圖的預期輸出，例如:

Below is the expected output from view for example:

ID_ISIN   QUOTE_CRNY   DESCR           RATIO              ALLOCATIONASSETTYPE
L000123    USD              O              0.0769          Other total
L000129    USD              O              0.0669          Other total
D123458    USD              O              0.64039         Other total
M123456    USD              O              5.64039         Other total
F563458    USD              C              0.84039         Other total
G123456    USD              null           0.04039         Other total
L000123    USD              C              5.0769          Other total

我可以根據這個條件創建視圖嗎?

Can i create View based on this conditions ?

推薦答案

我提出了以下查詢，請檢查我已對查詢發表評論.詢問是否不符合您的要求或進行任何澄清.

I have come up with below query, Please check I have put comments on the query. Ask if doesn't meet you r requirement or for any clarification.

CREATE VIEW v_combined_data AS
WITH combined_data
AS
(
SELECT t1.fund_isin
      ,t1.fund_quote_crny
      ,t1.member_descr
      ,t1.member_ratio
      ,t1.allocationassettype
      ,t2.fund_isin fund_isin_tst
      ,t2.fund_quote_crny fund_quote_crny_tst
      ,t2.member_descr member_descr_tst
      ,t2.member_ratio member_ratio_tst
      ,t2.allocationassettype allocationassettype_tst
FROM   is_id t1
FULL   OUTER JOIN is_id_tst t2
ON     t1.fund_isin = t2.fund_isin
AND    t1.fund_quote_crny = t2.fund_quote_crny
AND    t1.member_descr = t2.member_descr
)
-- for member_descr = 'O' and for common fund_isin field value from both tables, 
-- check the member_ratio field and take only the row where member_ratio field value is low.
SELECT d.fund_isin
      ,d.fund_quote_crny
      ,d.member_descr
      ,LEAST(d.member_ratio,d.member_ratio_tst) member_ratio
      ,d.allocationassettype
  FROM combined_data d
WHERE d.member_descr = 'O'
  AND d.fund_isin IS NOT NULL 
  AND d.fund_isin_tst IS NOT NULL
UNION ALL
--for member_descr = 'O' and if the fund_isin exist in one table but not in another then take those rows(bidirectional)
--exists in IS_ID and not in IS_ID_TST
SELECT d.fund_isin
      ,d.fund_quote_crny
      ,d.member_descr
      ,d.member_ratio
      ,d.allocationassettype
  FROM combined_data d
WHERE d.member_descr = 'O'
  AND NOT EXISTS (SELECT 1
                    FROM combined_data ci
                   WHERE ci.fund_isin_tst = d.fund_isin
                     AND ci.fund_quote_crny_tst = d.fund_quote_crny
                     AND ci.member_descr_tst = 'O')
UNION ALL
--for member_descr = 'O' and if the fund_isin exist in one table but not in another then take those rows(bidirectional)
--exists in IS_ID_TST and not in IS_ID
SELECT d.fund_isin_tst
      ,d.fund_quote_crny_tst
      ,d.member_descr_tst
      ,d.member_ratio_tst
      ,d.allocationassettype_tst
  FROM combined_data d
WHERE d.member_descr_tst = 'O'
  AND NOT EXISTS (SELECT 1
                    FROM combined_data ci
                   WHERE ci.fund_isin = d.fund_isin_tst
                     AND ci.fund_quote_crny = d.fund_quote_crny_tst
                     AND ci.member_descr = 'O')
UNION ALL
--for all the rows where member_descr ! = 'O', take all those rows from table IS_ID_TST
SELECT d.fund_isin_tst
      ,d.fund_quote_crny_tst
      ,d.member_descr_tst
      ,d.member_ratio_tst
      ,d.allocationassettype_tst
  FROM combined_data d
WHERE d.fund_isin_tst IS NOT NULL
  AND (d.member_descr_tst != 'O' OR d.member_descr_tst IS NULL);

這篇關于根據兩個數據庫表之間的數據比較創建oracle視圖的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！