将一个多表关联的条件查询中的多表通过 create select 转化成一张单表的sql、改为会话级别临时表 【我】

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

将结果改为创建一个会话级别的临时表：

-- 根据下面这两个sql

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';

 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;

--得到下面创建临时表的SQL

-- 创建回话级别临时表

CREATE GLOBAL TEMPORARY table REVENUE_ALL2

(

  STD_PRVNCE_CD    VARCHAR2(20),

  STD_LATN_CD      VARCHAR2(20),

  CUST_ID          VARCHAR2(20),

  PROD_TYPE        VARCHAR2(20),

  PARTY_ID         NUMBER(16),

  TY_12            NUMBER(16,2),

  TY_11            NUMBER(16,2),

  TY_10            NUMBER(16,2),

  TY_9             NUMBER(16,2),

  TY_8             NUMBER(16,2),

  TY_7             NUMBER(16,2),

  TY_6             NUMBER(16,2),

  TY_5             NUMBER(16,2),

  TY_4             NUMBER(16,2),

  TY_3             NUMBER(16,2),

  TY_2             NUMBER(16,2),

  TY_1             NUMBER(16,2),

  LY_12            NUMBER(16,2),

  LY_11            NUMBER(16,2),

  LY_10            NUMBER(16,2),

  LY_9             NUMBER(16,2),

  LY_8             NUMBER(16,2),

  LY_7             NUMBER(16,2),

  LY_6             NUMBER(16,2),

  LY_5             NUMBER(16,2),

  LY_4             NUMBER(16,2),

  LY_3             NUMBER(16,2),

  LY_2             NUMBER(16,2),

  LY_1             NUMBER(16,2),

  IDENTITY_TYPE    NUMBER(4),

  INDUSTRY_TYPE_ID NUMBER(16)

)

ON COMMIT PRESERVE ROWS;

--向临时表中插数据

insert into REVENUE_ALL2 select r.*, p.IDENTITY_TYPE, o.INDUSTRY_TYPE_ID

  from (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') 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_ALL2;