创建 Oracle 视图以根据条件比较数据

2021-09-17 00:00:00 select oracle view

我有下表:

    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;

相关文章