将一个多表关联的条件查询中的多表通过 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;