将一个多表关联的条件查询中的多表通过 create select 转化成一张单表的sql 【我】

将一个多表关联的条件查询中的多表通过 create   select  转化成一张单表的sql

select count(1) from REVENUE_STATISTICS_RES2;
select count(1) from REVENUE_STATISTICS_RES;


select * from eda_cust_inc;

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;



SELECT /*+PARALLEL(12)*/
        count(1)
         FROM EDA_CUST_INC e, CUST_CP_MERGE m

         WHERE 1 = 1
           and e.CUST_ID = m.CUST_ID
           AND e.STD_LATN_CD = m.STD_LATN_CD
           and m.PARTY_ID IS NOT NULL
           AND UPPER(m.PARTY_ID) != 'NULL';



CREATE TABLE revenue_cp AS SELECT /*+PARALLEL(12)*/
       e.STD_PRVNCE_CD STD_PRVNCE_CD,
       e.STD_LATN_CD STD_LATN_CD,
       e. CUST_ID ,e.PROD_TYPE,
       m.party_id party_id,
        e.TY_12   TY_12,
        e.TY_11   TY_11,
        e.TY_10   TY_10,
        e.TY_9    TY_9 ,
        e.TY_8    TY_8 ,
        e.TY_7    TY_7 ,
        e.TY_6    TY_6 ,
        e.TY_5    TY_5 ,
        e.TY_4    TY_4 ,
        e.TY_3    TY_3 ,
        e.TY_2    TY_2 ,
        e.TY_1    TY_1 ,
        e.LY_12   LY_12,
        e.LY_11   LY_11,
        e.LY_10   LY_10,
        e.LY_9    LY_9 ,
        e.LY_8    LY_8 ,
        e.LY_7    LY_7 ,
        e.LY_6    LY_6 ,
        e.LY_5    LY_5 ,
        e.LY_4    LY_4 ,
        e.LY_3    LY_3 ,
        e.LY_2    LY_2 ,
        e.LY_1    LY_1
         FROM EDA_CUST_INC e, CUST_CP_MERGE m
         WHERE 1 = 1
           and e.CUST_ID = m.CUST_ID
           AND e.STD_LATN_CD = m.STD_LATN_CD
           and m.PARTY_ID IS NOT NULL
           AND UPPER(m.PARTY_ID) != 'NULL';


select * from revenue_cp;
select count(1) from revenue_cp;


select r.*, p.IDENTITY_TYPE, o.INDUSTRY_TYPE_ID
  from revenue_cp r
  left join party p on r.party_id=p.party_id
  left join party_org o on r.party_id = o.party_id;

select count(1)
  from revenue_cp r;


select count(1)
  from revenue_cp r
  left join party p on r.party_id=p.party_id
  left join party_org o on r.party_id = o.party_id;


CREATE TABLE revenue_all as select r.*, p.IDENTITY_TYPE, o.INDUSTRY_TYPE_ID
  from revenue_cp r
  left join party p on r.party_id=p.party_id
  left join party_org o on r.party_id = o.party_id;


select * from revenue_all;


SELECT /*+PARALLEL(12)*/
 COUNT(CUST_ID) CUSTNUM,
 COUNT(DISTINCT(PARTY_ID)) PARTYNUM,
 SUM(TY_1) TY1,
 SUM(TY_2) TY2,
 SUM(TY_3) TY3,
 SUM(TY_4) TY4,
 SUM(TY_5) TY5,
 SUM(TY_6) TY6,
 SUM(TY_7) TY7,
 SUM(TY_8) TY8,
 SUM(TY_9) TY9,
 SUM(TY_10) TY10,
 SUM(TY_11) TY11,
 SUM(TY_12) TY12,
 SUM(LY_1) LY1,
 SUM(LY_2) LY2,
 SUM(LY_3) LY3,
 SUM(LY_4) LY4,
 SUM(LY_5) LY5,
 SUM(LY_6) LY6,
 SUM(LY_7) LY7,
 SUM(LY_8) LY8,
 SUM(LY_9) LY9,
 SUM(LY_10) LY10,
 SUM(LY_11) LY11,
 SUM(LY_12) LY12
  from revenue_all
 where std_prvnce_cd = 8350000
   and std_latn_cd = 8350102
   and prod_type = 10
   and identity_type = 1
   and INDUSTRY_TYPE_ID = 207;


SELECT /*+PARALLEL(12)*/
 COUNT(CUST_ID) CUSTNUM,
 COUNT(DISTINCT(PARTY_ID)) PARTYNUM,
 SUM(TY_1) TY1,
 SUM(TY_2) TY2,
 SUM(TY_3) TY3,
 SUM(TY_4) TY4,
 SUM(TY_5) TY5,
 SUM(TY_6) TY6,
 SUM(TY_7) TY7,
 SUM(TY_8) TY8,
 SUM(TY_9) TY9,
 SUM(TY_10) TY10,
 SUM(TY_11) TY11,
 SUM(TY_12) TY12,
 SUM(LY_1) LY1,
 SUM(LY_2) LY2,
 SUM(LY_3) LY3,
 SUM(LY_4) LY4,
 SUM(LY_5) LY5,
 SUM(LY_6) LY6,
 SUM(LY_7) LY7,
 SUM(LY_8) LY8,
 SUM(LY_9) LY9,
 SUM(LY_10) LY10,
 SUM(LY_11) LY11,
 SUM(LY_12) LY12
  from revenue_all
 where std_prvnce_cd = 8350000
   and std_latn_cd = 8350102
   and prod_type is null
   and identity_type is null
   and INDUSTRY_TYPE_ID is null;