SQL编写对照（Oracle-GreenPlum).doc

ORACLE SQL 测试内容对照 原始SQL(ORACLE)SQL(greenplum)create table O_PROC_ATTR(PROC_ORD_ID NUMBER(10) not null, ATTR_SEQ NUMBER(10) not null, ATTR_OBJ_TYPE VARCHAR2(6), ATTR_PROC_SEQ NUMBER(10), OBJ_ID NUMBER(10), ATTR_ATTR_TYPE VARCHAR2(6), ATTR_PROC_TYPE VARCHAR2(6), ATTR_VALUES VARCHAR2(200), ATTR_PARAM_A VARCHAR2(4000), ATTR_PARAM_B VARCHAR2(4000), ATTR_STATE VARCHAR2(6), ATTR_TYPE VARCHAR2(6), AREA_ID NUMBER(10), REAL_MODIFY_DATE DATE, INST_ID VARCHAR2(2), ETL_DATE DATE)alter table O_PROC_ATTR add constraint PK_O_PROC_ATTR primary key (PROC_ORD_ID, ATTR_SEQ)create table O_PROC_ATTR( PROC_ORD_ID NUMERIC(10) not null, ATTR_SEQ NUMERIC(10) not null, ATTR_OBJ_TYPE VARCHAR(6), ATTR_PROC_SEQ NUMERIC(10), OBJ_ID NUMERIC(10), ATTR_ATTR_TYPE VARCHAR(6), ATTR_PROC_TYPE VARCHAR(6), ATTR_VALUES VARCHAR(200), ATTR_PARAM_A VARCHAR(4000), ATTR_PARAM_B VARCHAR(4000), ATTR_STATE VARCHAR(6), ATTR_TYPE VARCHAR(6), AREA_ID NUMERIC(10), REAL_MODIFY_DATE TIMESTAMP, INST_ID VARCHAR(2), ETL_DATE TIMESTAMP, constraint PK_O_PROC_ATTR primary key (PROC_ORD_ID, ATTR_SEQ)DISTRIBUTED BY (PROC_ORD_ID, ATTR_SEQ);create table MDSE( MDSE_ID NUMBER(10) not null, MDSE_SPEC_ID NUMBER(10) not null, PROPERTY_CUSTID NUMBER(10), CREATE_DATE DATE not null, EFF_DATE DATE, EXP_DATE DATE, STATE VARCHAR2(6), PROD_ID NUMBER(10) not null, PROD_FEA_ID NUMBER(10), MDSE_TYPE VARCHAR2(6), MODIFY_DATE DATE, REGION NUMBER(10), MDSE_SERV_NUMBER VARCHAR2(40), ATTR_SORT VARCHAR2(6), TRIAL_EFF_DATE DATE, TRIAL_EXP_DATE DATE, REAL_MODIFY_DATE DATE, ETL_DATE DATE, RELA_SERIAL VARCHAR2(30)create table MDSE( MDSE_ID NUMERIC(10) not null, MDSE_SPEC_ID NUMERIC(10) not null, PROPERTY_CUSTID NUMERIC(10), CREATE_DATE TIMESTAMP not null, EFF_DATE VARCHAR(30), EXP_DATE TIMESTAMP, STATE VARCHAR(6), PROD_ID NUMERIC(10) not null, PROD_FEA_ID NUMERIC(10), MDSE_TYPE VARCHAR(6), MODIFY_DATE TIMESTAMP, REGION NUMERIC(10), MDSE_SERV_NUMBER VARCHAR(40), ATTR_SORT VARCHAR(6), TRIAL_EFF_DATE VARCHAR(30), TRIAL_EXP_DATE VARCHAR(30), REAL_MODIFY_DATE TIMESTAMP, ETL_DATE TIMESTAMP, RELA_SERIAL VARCHAR(30), CONSTRAINT PK_MDSE PRIMARY KEY(MDSE_ID)DISTRIBUTED BY (MDSE_ID,PROD_ID)partition by list (REGION)( partition P_591 values (2, 11, 12, 13, 14, 15, 16, 17, 18, 19), partition P_592 values (83, 3, 20, 21), partition P_593 values (4, 22, 23, 24, 25, 26, 27, 28, 29, 30), partition P_594 values (5, 31, 32, 81, 82), partition P_595 values (84, 85, 86, 6, 33, 34, 35, 36, 37, 38, 39, 40, 80), partition P_596 values (7, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50), partition P_597 values (8, 51, 52, 53, 54, 55, 56, 57), partition P_598 values (9, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68), partition P_599 values (10, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78), default partition other);create table PROD( PROD_ID NUMBER(10) not null, PROPERTY_CUSTID NUMBER(10), CUST_ID NUMBER(10), PROD_SPEC_ID NUMBER(10) not null, PROD_SPEC_TYPE VARCHAR2(6), CONTRACT VARCHAR2(30), CREATE_DATE DATE, MODIFY_DATE DATE, MODIFY_CAUSE VARCHAR2(6), STAFF VARCHAR2(50), STATE VARCHAR2(6), SERVICE_TYPE VARCHAR2(6), SERVICE_CODE VARCHAR2(500), ADDRESS_ID VARCHAR2(15), STOP_STATUS VARCHAR2(6), EXCH_ID VARCHAR2(11), ROLE_ID NUMBER(10), ZIPCODE VARCHAR2(8), REGION NUMBER(10), AREA_CODE VARCHAR2(6), ACCT_CYCLE VARCHAR2(6), PAY_TYPE VARCHAR2(6), TERM_TYPE NUMBER(10), RENT_DATE DATE, REMARK VARCHAR2(1024), ACCOUNT VARCHAR2(500), ATTR_SORT VARCHAR2(6), LINKMAN NUMBER(10), RENT_TYPE VARCHAR2(6), ADDRESS_NAME VARCHAR2(500), REAL_MODIFY_DATE DATE, ETL_DATE DATE)create table PROD( PROD_ID NUMERIC(10) not null, PROPERTY_CUSTID NUMERIC(10), CUST_ID NUMERIC(10), PROD_SPEC_ID NUMERIC(10) not null, PROD_SPEC_TYPE VARCHAR(6), CONTRACT VARCHAR(30), CREATE_DATE TIMESTAMP, MODIFY_DATE TIMESTAMP, MODIFY_CAUSE VARCHAR(6), STAFF VARCHAR(50), STATE VARCHAR(6), SERVICE_TYPE VARCHAR(6), SERVICE_CODE VARCHAR(500), ADDRESS_ID VARCHAR(15), STOP_STATUS VARCHAR(6), EXCH_ID VARCHAR(11), ROLE_ID NUMERIC(10), ZIPCODE VARCHAR(8), REGION NUMERIC(10), AREA_CODE VARCHAR(6), ACCT_CYCLE VARCHAR(6), PAY_TYPE VARCHAR(6), TERM_TYPE NUMERIC(10), RENT_DATE TIMESTAMP, REMARK VARCHAR(1024), ACCOUNT VARCHAR(500), ATTR_SORT VARCHAR(6), LINKMAN NUMERIC(10), RENT_TYPE VARCHAR(6), ADDRESS_NAME VARCHAR(500), REAL_MODIFY_DATE TIMESTAMP, ETL_DATE TIMESTAMP, RELA_SERIAL VARCHAR(30), constraint pk_prod primary key(prod_id)DISTRIBUTED BY (PROD_ID)partition by list (REGION)( partition P_591 values (2, 11, 12, 13, 14, 15, 16, 17, 18, 19), partition P_592 values (83, 3, 20, 21), partition P_593 values (4, 22, 23, 24, 25, 26, 27, 28, 29, 30), partition P_594 values (5, 31, 32, 81, 82), partition P_595 values (84, 85, 86, 6, 33, 34, 35, 36, 37, 38, 39, 40, 80), partition P_596 values (7, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50), partition P_597 values (8, 51, 52, 53, 54, 55, 56, 57), partition P_598 values (9, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68), partition P_599 values (10, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78), default partition other);create table MDSE_SPEC( MDSE_SPEC_ID NUMBER(10) not null,-销售品规格ID STANDARD_CODE VARCHAR2(40), TYPE VARCHAR2(6), NAME VARCHAR2(100) not null 规格名称) create table MDSE_SPEC( MDSE_SPEC_ID NUMERIC(10) not null, STANDARD_CODE VARCHAR(40), TYPE VARCHAR(6), NAME VARCHAR(100) not null ) DISTRIBUTED BY (MDSE_SPEC_ID);create table PROD_SPEC( PROD_SPEC_ID NUMBER(10) not null,-产品规格ID CODE VARCHAR2(40), REMARKS VARCHAR2(500), EFF_DATE DATE not null, EXP_DATE DATE, NAME VARCHAR2(250) -产品规格名称)create table PROD_SPEC( PROD_SPEC_ID NUMERIC(10) not null, CODE VARCHAR(40), REMARKS VARCHAR(500), EFF_DATE TIMESTAMP not null, EXP_DATE TIMESTAMP, NAME VARCHAR(250) )DISTRIBUTED BY (PROD_SPEC_ID);create table MDSE_TYPE( MDSE_TYPE VARCHAR2(6) not null,-销售品类型 MDSE_TYPE_NAME VARCHAR2(80) 销售品类型名称)create table MDSE_TYPE( MDSE_TYPE VARCHAR(6) not null, MDSE_TYPE_NAME VARCHAR(80) )DISTRIBUTED BY (MDSE_TYPE);create table P_AREA( AREA_ID NUMBER not null,-地区ID NAME VARCHAR2(80) 地区名称)create table P_AREA( AREA_ID NUMERIC not null, NAME VARCHAR(80) )DISTRIBUTED BY (AREA_ID);SELECT COUNT(*) FROM O_PROC_ATTRSELECT COUNT(*) FROM O_PROC_ATTR;select * from ods2.O_PROC_ATTR where ATTR_VALUES IN(999999970,202909)select * from O_PROC_ATTR where ATTR_VALUES IN(999999970,202909);select * from ods2.O_PROC_ATTR where ATTR_VALUES IN(999999970,202909)select * from O_PROC_ATTR where ATTR_VALUES IN(999999970,202909);SELECT COUNT(*) FROM MDSESELECT COUNT(*) FROM MDSE;SELECT COUNT(*) FROM PRODSELECT COUNT(*) FROM PROD;selectCOUNT(*) fromproda,mdseb d_id=d_idselect COUNT(*) from prod a,mdse b where d_id = d_id;selectCOUNT(*) fromproda,mdseb d_id=d_id and b.create_date between sysdate -50 and sysdate -20select COUNT(*) from prod a,mdse b where d_id = d_id and b.create_date between current_date-50 and current_date-20;SELECT * FROM MDSE A,MDSE_SPEC B,MDSE_TYPE C,P_AREA PWHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_ID AND A.MDSE_TYPE = B.MDSE_TYPE AND A.AREA_ID = P.AREA_IDSELECT * FROM MDSE A,MDSE_SPEC B,MDSE_TYPE C,P_AREA PWHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_ID AND A.MDSE_TYPE = B.TYPE AND A.region = P.AREA_ID;SELECT * FROM MDSE A,MDSE_SPEC B,MDSE_TYPE C,P_AREA PWHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_ID(+) AND A.MDSE_TYPE = B.MDSE_TYPE(+) AND A.AREA_ID = P.AREA_ID(+)SELECT * FROM MDSE A left outer join MDSE_SPEC B on (A.MDSE_SPEC_ID = B.MDSE_SPEC_ID)left outer join MDSE_TYPE Con (A.MDSE_TYPE=C.MDSE_TYPE)left outer join P_AREA Pon (A.region = P.AREA_ID);SELECT PROD_SPEC_ID,COUNT(*) FROM PROD GROUP BY PROD_SPEC_IDSELECT PROD_SPEC_ID,COUNT(*) FROM PROD GROUP BY PROD_SPEC_ID;SELECT COUNT(DISTINCT PROPERTY_CUST_ID) FROM MDSEGROUP BY COUNT(DISTINCT PROPERTY_CUST_ID) 10SELECT MDSE_SPEC_ID,COUNT(DISTINCT PROPERTY_CUSTID) FROM MDSE GROUP BY MDSE_SPEC_ID HAVING COUNT(DISTINCT PROPERTY_CUSTID) 10;SELECT SUM(CASE WHEN MDSE_TYPE =101 THEN 1 WHEN MDSE_TYPE =102 THEN 1) CNT_SUM,MAX(MDSE_SPEC_ID),MIN(PROD_ID) FROM MDSE WHERE CREATE_DATE BETWEEN SYSDATE -30 AND SYSDATESELECT SUM(CASE WHEN MDSE_TYPE =101 THEN 1 WHEN MDSE_TYPE =102 THEN 1 END ) CNT_SUM,MAX(MDSE_SPEC_ID),MIN(PROD_ID) FROM MDSE WHERE CREATE_DATE BETWEEN current_date-30 AND current_date;SELECT * FROM (SELECT PROD_ID FROM PROD WHERE CREATE_DATE BETWEEN SYSDATE -50 AND SYSDATE -30UNION SELECT PROD_ID FROM MDSE WHERE CREATE_DATE BETWEEN SYSDATE -50 AND SYSDATE -30 )SELECT * FROM (SELECT PROD_ID FROM PROD WHERE CREATE_DATE BETWEEN current_date-50 AND current_date-30UNION SELECT PROD_ID FROM MDSE WHERE CREATE_DATE BETWEEN current_date-50 AND current_date-30 ) as t；SELECT COUNT(*) FROM (SELECT CUST_ID,ROW_NUMBER()OVER(PARTITION BY CUST_ID ORDER BY CREATE_DATE DESC) ROW_NUM FROM ODS2.PROD ) WHERE ROW_NUM =1SELECT COUNT(*) FROM (SELECT CUST_ID,ROW_NUMBER()OVER(PARTITION BY CUST_ID ORDER BY CREATE_DATE DESC) ROW_NUM FROM PROD ) as t WHERE ROW_NUM =1;SELECT COUNT(*) FROM (SELECT TO_NUMBER(TO_CHAR(CREATE_DATE,YYYYMMDD) STAT_DATE,COUNT(DISTINCT CUST_ID) FROM ODS2.PROD WHERE CREATE_DATE SYSDATE -365 GROUP BY TO_NUMBER(TO_CHAR(CREATE_DATE,YYYYMMDD) )SELECT COUNT(*) FROM (SELECT cast(substr(cast(create_date as varchar(10),1,4)|substr(cast(create_date as varchar(10),6,2)|substr(cast(create_date as varchar(10),9,2) as numeric(8) STAT_DATE,COUNT(DISTINCT CUST_ID) FROM PROD WHERE CREATE_DATE current_date-365 GROUP BY cast(substr(cast(create_date as varchar(10),1,4)|substr(cast(create_date as varchar(10),6,2)|substr(cast(create_date as varchar(10),9,2) as numeric(8) as t;CREATE TABLE T1 AS SELECT * from O_PROC_ATTR where RELA_MODIFY_DATE between sysdate-50 and sysdate-20CREATE TABLE T1 AS SELECT * from O_PROC_ATTR where REAL_MODIFY_DATE between current_date-50 and current_date-20DISTRIBUTED BY (PROC_ORD_ID, ATTR_SEQ);SELECT COUNT(*)FROM MDSE WHERE CREATE_DATE BETWEEN SYSDATE-20 AND SYSDATE -10SELECT COUNT(*)FROM MDSEWHERE CREATE_DATE BETWEEN CURRENT_DATE-20 AND CURRENT_DATE-10;DELETE FROM MDSE WHERE CREATE_DATE BETWEEN SYSDATE-20 AND SYSDATE -10DELETE FROM MDSE WHERE CREATE_DATE BETWEEN CURRENT_DATE-20 AND CURRENT_DATE -10;DELETE FROM MDSE M WHERE EXISTS (SELECT 1 FROM PROD P WHERE M.PROD_ID = P.PROD_ID AND P.CRAETE_DATE BEWTEEN SYSDATE-50 AND SYSDATE -30 )create table MDSE_TEMPASselect M.* from MDSE M LEFT OUTER JOIN(SELECT * FROM PROD P WHERE P.CREATE_DATE BETWEEN CURRENT_DATE-50 AND CURRENT_DATE-30) AS TON(M.PROD_ID=T.PROD_ID)WHERE T.PROD_ID IS NULLDISTRIBUTED BY (MDSE_ID,PROD_ID); DROP TABLE MDSE;ALTER TABLE MDSE_TEMP ADD CONSTRAINT PK_MDSE PRIMARY KEY(MDSE_ID);ALTER TABLE MDSE_TEMP RENAME TO MDSE;CREATE TABLE T_MDSE AS SELECT * FROM ODS2.MDSE WHERE CRATE_DATE BETWEEN SYSDATE -50 AND SYSDATE -30CREATE TABLE T_MDSE AS SELECT * FROM MDSE WHERE CREATE_DATE BETWEEN current_date-50 AND current_date-30DISTRIBUTED BY (MDSE_ID,PROD_ID);ALTER TABLE T_MDSE ADD CONSTRAINT PK_T_MDSE PRIMARY KEY(MDSE_ID);UPDATE MDSE T1 SET (MDSE_ID, MDSE_SPEC_ID,PROPERTY_CUSTID ,CREATE_DATE, EFF_DATE,EXP_DATE,STATE,PROD_ID,PROD_FEA_ID,MDSE_TYPE,MODIFY_DATE,REGION,MDSE_SERV_NUMBER,ATTR_SORT,TRIAL_EFF_DATE,TRIAL_EXP_DATE,REAL_MODIFY_DATE,ETL_DATE,RELA_SERIAL) = (SELECT MDSE_ID, MDSE_SPEC_ID,PROPERTY_CUSTID ,CREATE_DATE, EFF_DATE,EXP_DATE,STATE,PROD_ID,PROD_FEA_ID,MDSE_TYPE,MODIFY_DATE,REGION,MDSE_SERV_NUMBER,ATTR_SORT,TRIAL_EFF_DATE,TRIAL_EXP_DATE,REAL_MODIFY_DATE ,ETL_DATE,RELA_SERIAL FROM T_MDSE T2 WHERE T1.MDSE_ID = T2.MDSE_ID )UPDATE ONLY MDSE T1 SET (PROD_ID,MDSE_SPEC_ID,PROPERTY_CUSTID ,CREATE_DATE, EFF_DATE, EXP_DATE,STATE,PROD_FEA_ID,MDSE_TYPE,MODIFY_DATE,REGION,MDSE_SERV_NUMBER,ATTR_SORT,TRIAL_EFF_DATE,TRIAL_EXP_DATE,REAL_MODIFY_DATE,ETL_DATE,RELA_SERIAL) = (T2.PROD_ID,T2.MDSE_SPEC_ID,T2.PROPERTY_CUSTID,T2.CREATE_DATE,T2.EFF_DATE,T2.EXP_DATE,T2.STATE,T2.PROD_FEA_ID,T2.MDSE_TYPE,T2.MODIFY_DATE,T2.REGION,T2.MDSE_SERV_NUMBER,T2.ATTR_SORT,T2.TRIAL_EFF_DATE,T2.TRIAL_EXP_DATE,T2.REAL_MODIFY_DATE ,T2.ETL_DATE,T2.RELA_SE