問題描述

我有下表:

    CREATE TABLE
        IS_ID
        (        
            FUND_ISIN VARCHAR2(12) NOT NULL,
            FUND_QUOTE_CRNY VARCHAR2(5),       
            MEMBER_DESCR VARCHAR2(5),
            MEMBER_RATIO NUMBER(19,8),
            ALLOCATIONASSETTYPE VARCHAR2(100)
        );
    
    CREATE TABLE
        IS_ID_TST
        (        
            FUND_ISIN VARCHAR2(12) NOT NULL,
            FUND_QUOTE_CRNY VARCHAR2(5),       
            MEMBER_DESCR VARCHAR2(5),
            MEMBER_RATIO NUMBER(19,8),
            ALLOCATIONASSETTYPE VARCHAR2(100)
        );

    

我想創建這樣的視圖:

1. 對于兩個表中共同的 fund_isin 字段值，檢查 member_ratio 字段的 member_descr = 'O' 并獲取所有表中的 fund_isin 行，其中 member_ratio 字段值較低.對于 member_descr = 'O'，如果 IS_ID_TST 表中的 member_ratio 對于任何 fund_isin 小于 0，則總是取IS_ID_TST表中的所有數據(在這種情況下，我們不需要比較IS_ID表中的數據，因為成員比例低)

1. for common fund_isin field value from both tables, check the member_ratio field for member_descr = 'O'and take all the rows for fund_isin from table where member_ratio field value is low. For member_descr = 'O', if the member_ratio in IS_ID_TST table is less than 0 for any fund_isin then always take all the data from IS_ID_TST table(in this case we dont need to compare data from IS_ID table for low member ratio)

如果 fund_isin 存在于一個表中但不存在于另一個表中，則獲取所有這些行(雙向).

if the fund_isin exist in one table but not in another then take all those rows(bidirectional).

對于所有其他 fund_isin，僅從表 IS_ID_TST 表中獲取所有這些行(這可能涵蓋第 1 點和第 2 點)

for all the other fund_isin, take all those rows only from table IS_ID_TST table(this might cover in point 1 and 2 )

推薦答案

您能否檢查以下查詢，我已經在 with 子句中制作了所有案例，然后將其結合起來.

Could you check following query, I have made all cases within with clause and then make union out of it.

- 通過聊天與 OP 討論和澄清

我們不再需要 full join 并且通過根據每種情況訪問表來重寫它.

We do not need full join any more and by accessing the table per each case it is re-written.

-- case 1
-- when fund_isin with member_ratio = 'O' present in both is_id and is_id_tst table
-- and the value of is_id.member_ratio < is_id_tst.memebr_ratio
-- logic --
-- the from clasuse says take all the records from is_id table 
-- by corelate the fund_isin (t1.fund_isin = t.fund_isin)
-- the subquery then finds record by joining both table is_id and is_id_tst for member_ratio = 'O'
-- and where the member_ratio is smaller (is_id_tst.member_ratio > is_id.member_ratio)
-- extra condition on is_id_tst table is the member_ratio value should be greater than 0 for member_descr='O'
WITH ratio_lower_is_id
AS
(SELECT *
   FROM is_id t
  WHERE EXISTS 
  (SELECT 1
     FROM is_id_tst t2
     JOIN is_id t1
       ON t2.fund_isin = t1.fund_isin
    WHERE t1.fund_isin = t.fund_isin
      AND t2.member_descr = 'O'
      AND t1.member_descr = 'O'
      AND t2.member_ratio > 0
      AND t2.member_ratio > 
          t1.member_ratio)
),
-- case 2
-- applies the same logic as in case 1 but then take records from is_id_tst table
-- where the member_ratio having lower value for record with member_descr='O'
-- in comparison with the record present in is_id table for memebr_descr='O'
ratio_lower_is_id_tst
AS
(SELECT *
   FROM is_id_tst t
  WHERE t.member_ratio > 0
    AND EXISTS 
  (SELECT 1
     FROM is_id t2
     JOIN is_id_tst t1
       ON t2.fund_isin = t1.fund_isin
    WHERE t1.fund_isin = t.fund_isin
      AND t2.member_descr = 'O'
      AND t1.member_descr = 'O'
      AND t2.member_ratio > 
          t1.member_ratio)
),
-- case 3
-- take all records from is_id_tst table for all each unique fund_isin 
-- where the member_ratio value is < 0 for record member_descr='O'
-- and is avaialble in is_id_tst table irrespective of what record for the same
-- fund_isin available in is_id table
ratio_minus_is_id_tst
AS
(SELECT *
   FROM is_id_tst t
  WHERE EXISTS 
  (SELECT 1
     FROM is_id_tst t1
    WHERE t1.fund_isin = t.fund_isin
      AND t1.member_descr = 'O'
      AND t1.member_ratio < 0)
),
-- case 4
-- take all the records from is_id table 
-- where the fund_isin is not available in is_id_tst table
only_in_is_id
AS
(
SELECT *
  FROM is_id t1
 WHERE NOT EXISTS
   (SELECT 1 
      FROM is_id_tst t2
     WHERE t2.fund_isin = t1.fund_isin)
),
-- case 5
-- take all the records from is_id_tst table
-- where the fund_isin is not available in is_id table
only_in_is_id_tst
AS
(
SELECT *
  FROM is_id_tst t1
 WHERE NOT EXISTS
   (SELECT 1 
      FROM is_id t2
     WHERE t2.fund_isin = t1.fund_isin)
)
-- finally once all the sets as per each case available
-- take each of them and do a union all for the final result set
-- one level sub query required only if we want to sort the result otherwise can be removed
-- and only union all of all sets from with clause is enough
select *
  from 
(
-- case1
select *
  from ratio_lower_is_id
union all
-- case 2
select *
  from ratio_lower_is_id_tst
union all
-- case 3
select *
  from ratio_minus_is_id_tst
union all
-- case 4
select *
  from only_in_is_id
union all
-- case 5
select *
  from only_in_is_id_tst
)
order by fund_isin;

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