用with改写优化sql
来源：互联网 发布：数据库存储过程作用 编辑：程序博客网 时间：2024/05/16 17:23
网友发来一案例，sql如下，表名及部分字段名作了处理，这个sql要跑20分钟。因为本次改写与plan无关，所以plan也就不发了
WITH SAWITH0 AS (select distinct T_A.T_A_SHORT_NAME as c1, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2, T_B.CAL_MONTH_NAME as c3, T_A.EFFECTIVE_MONTH_ACT as c4, T_A.EFFECTIVE_MONTH_BGT as c5, T_A.T_A_CODE as c6, T_A.T_A_COOPER as c7, T_B.CAL_MONTH_ID as c8 from T_B T_B, T_C T_C, (SELECT CHAIN_T_A_SK, HEADER_ID, OFFICE_CODE, T_A_CODE, T_A_SHORT_NAME, T_A_NAME, T_A_ADDRESS, T_A_PHONE, T_A_PROPERITY, COMPANY_PROPERTY, T_A_AREA, PROVINCE_SEQ, T_A_PROVINCE, CITY_SEQ, T_A_CITY, T_A_COUNTY, T_A_COOPER, T_A_BRAND, T_A_NATURE, LEASE_BUILDING_AREA, CONS_BUILDING_AREA, ROOM_QUANTITY, SEATS_QUANTITY, MEAL_QUANTITY, BUSINESS_LICENSE_DATE, SIGN_DATE, START_DATE, END_DATE, case when SIGN_DATE >= PRE_BUSINESS_DATE then SIGN_DATE else PRE_BUSINESS_DATE end PRE_BUSINESS_DATE, BUSINESS_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, T_A_SEGMENT, T_A_AREA_1, T_A_TYPE, ORG_CODE, BUSINESS_LICENSE_END_DATE, PMS_CODE, ORG_ID, PRO_HANDOVER_DATE, FOREIGN_RENT_AREA, SIGN_YEAR, COMPANY_NAME, REMOVE_DATE, EFFECTIVE_DATE, EXPIRATION_DATE, CURRENT_FLAG, ETL_DATE, OLD_T_A_NAME, BRAND_CODE, EQUITY_NAME, (EQUITY_RATE / 100.00) AS EQUITY_RATE, nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE, floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN, floor(business_date - pre_business_date) AS DIFF_B_PRE, floor(business_date - sign_date) AS DIFF_B_SIGN, CASE WHEN TERMINATION_DATE IS NULL THEN 'N' ELSE 'Y' END IS_FLAG, TERMINATION_DATE, to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act, to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act, to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt, to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt FROM T_A, (select t.T_A_code u_T_A_code, max(t.effective_date) u_effective_date from T_A t group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t WHERE T_A.T_A_code = t.u_T_A_code and T_A.effective_date = t.u_effective_date) T_A where (T_C.T_A_CODE = T_A.T_A_CODE and T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and T_B.CAL_YEAR_ID = '2013' and T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and T_C.PERIOD_MON >= '2013-05' and (substr(T_C.T_A_CODE, 1, 1) in ('A', 'B')) and (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >= T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and T_B.CAL_MONTH_NAME >= '2013-05')),SACOMMON3694452 AS (select sum(T_C.AVHIRE_QTY_ACT) as c9, sum(T_C.RMHIRE_QTY_ACT) as c10, T_A.EFFECTIVE_MONTH_ACT as c11, T_A.EFFECTIVE_MONTH_BGT as c12, T_A.T_A_CODE as c13, T_A.T_A_COOPER as c14, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c15, T_B.CAL_MONTH_ID as c16, grouping_id(T_A.T_A_CODE, T_A.T_A_COOPER, T_A.EFFECTIVE_MONTH_ACT, T_A.EFFECTIVE_MONTH_BGT, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_B.CAL_MONTH_ID) as c17 from T_B T_B, T_C T_C, (SELECT CHAIN_T_A_SK, HEADER_ID, OFFICE_CODE, T_A_CODE, T_A_SHORT_NAME, T_A_NAME, T_A_ADDRESS, T_A_PHONE, T_A_PROPERITY, COMPANY_PROPERTY, T_A_AREA, PROVINCE_SEQ, T_A_PROVINCE, CITY_SEQ, T_A_CITY, T_A_COUNTY, T_A_COOPER, T_A_BRAND, T_A_NATURE, LEASE_BUILDING_AREA, CONS_BUILDING_AREA, ROOM_QUANTITY, SEATS_QUANTITY, MEAL_QUANTITY, BUSINESS_LICENSE_DATE, SIGN_DATE, START_DATE, END_DATE, case when SIGN_DATE >= PRE_BUSINESS_DATE then SIGN_DATE else PRE_BUSINESS_DATE end PRE_BUSINESS_DATE, BUSINESS_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, T_A_SEGMENT, T_A_AREA_1, T_A_TYPE, ORG_CODE, BUSINESS_LICENSE_END_DATE, PMS_CODE, ORG_ID, PRO_HANDOVER_DATE, FOREIGN_RENT_AREA, SIGN_YEAR, COMPANY_NAME, REMOVE_DATE, EFFECTIVE_DATE, EXPIRATION_DATE, CURRENT_FLAG, ETL_DATE, OLD_T_A_NAME, BRAND_CODE, EQUITY_NAME, (EQUITY_RATE / 100.00) AS EQUITY_RATE, nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE, floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN, floor(business_date - pre_business_date) AS DIFF_B_PRE, floor(business_date - sign_date) AS DIFF_B_SIGN, CASE WHEN TERMINATION_DATE IS NULL THEN 'N' ELSE 'Y' END IS_FLAG, TERMINATION_DATE, to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act, to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act, to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt, to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt FROM T_A, (select t.T_A_code u_T_A_code, max(t.effective_date) u_effective_date from T_A t group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t WHERE T_A.T_A_code = t.u_T_A_code and T_A.effective_date = t.u_effective_date) T_A where (T_B.CAL_MONTH_NAME < T_A.EXPIRATION_MONTH_ACT and T_B.CAL_YEAR_ID = '2013' and T_C.T_A_CODE = T_A.T_A_CODE and T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and T_B.CAL_MONTH_NAME >= T_A.EFFECTIVE_MONTH_ACT and T_B.CAL_MONTH_NAME >= '2013-05' and (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >= T_B.CAL_MONTH_NAME or T_A.TERMINATION_DATE is null)) group by grouping sets((cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_COOPER, T_A.T_A_CODE),(T_B.CAL_MONTH_ID, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_CODE))),SAWITH1 AS (select sum(T_C.AVHIRE_QTY_ACT) as c7, sum(T_C.RMHIRE_QTY_ACT) as c8, T_A.EFFECTIVE_MONTH_ACT as c9, T_A.EFFECTIVE_MONTH_BGT as c10, T_A.T_A_CODE as c11, T_A.T_A_COOPER as c12 from T_B T_B, T_C T_C, (SELECT CHAIN_T_A_SK, HEADER_ID, OFFICE_CODE, T_A_CODE, T_A_SHORT_NAME, T_A_NAME, T_A_ADDRESS, T_A_PHONE, T_A_PROPERITY, COMPANY_PROPERTY, T_A_AREA, PROVINCE_SEQ, T_A_PROVINCE, CITY_SEQ, T_A_CITY, T_A_COUNTY, T_A_COOPER, T_A_BRAND, T_A_NATURE, LEASE_BUILDING_AREA, CONS_BUILDING_AREA, ROOM_QUANTITY, SEATS_QUANTITY, MEAL_QUANTITY, BUSINESS_LICENSE_DATE, SIGN_DATE, START_DATE, END_DATE, case when SIGN_DATE >= PRE_BUSINESS_DATE then SIGN_DATE else PRE_BUSINESS_DATE end PRE_BUSINESS_DATE, BUSINESS_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, T_A_SEGMENT, T_A_AREA_1, T_A_TYPE, ORG_CODE, BUSINESS_LICENSE_END_DATE, PMS_CODE, ORG_ID, PRO_HANDOVER_DATE, FOREIGN_RENT_AREA, SIGN_YEAR, COMPANY_NAME, REMOVE_DATE, EFFECTIVE_DATE, EXPIRATION_DATE, CURRENT_FLAG, ETL_DATE, OLD_T_A_NAME, BRAND_CODE, EQUITY_NAME, (EQUITY_RATE / 100.00) AS EQUITY_RATE, nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE, floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN, floor(business_date - pre_business_date) AS DIFF_B_PRE, floor(business_date - sign_date) AS DIFF_B_SIGN, CASE WHEN TERMINATION_DATE IS NULL THEN 'N' ELSE 'Y' END IS_FLAG, TERMINATION_DATE, to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act, to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act, to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt, to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt FROM T_A, (select t.T_A_code u_T_A_code, max(t.effective_date) u_effective_date from T_A t group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t WHERE T_A.T_A_code = t.u_T_A_code and T_A.effective_date = t.u_effective_date) T_A where (T_C.T_A_CODE = T_A.T_A_CODE and T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and T_B.CAL_YEAR_ID = '2013' and T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >= T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and T_C.PERIOD_MON >= '2013-05' and T_B.CAL_MONTH_NAME >= '2013-05') group by T_A.T_A_CODE, T_A.T_A_COOPER, T_A.EFFECTIVE_MONTH_ACT, T_A.EFFECTIVE_MONTH_BGT),SAWITH2 AS (select D4.c7 as c1, D4.c8 as c2, D1.c7 as c3, D1.c1 as c4, D2.c9 as c5, D2.c10 as c6, D3.c10 as c7, D1.c2 as c8, D1.c3 as c9, D1.c4 as c10, D1.c5 as c11, D1.c6 as c12 from ((SAWITH0 D1 inner join SACOMMON3694452 D2 On SYS_OP_MAP_NONNULL(D2.c15) = SYS_OP_MAP_NONNULL(D1.c2) and D2.c12 = D1.c5 and D2.c11 = D1.c4 and SYS_OP_MAP_NONNULL(D2.c13) = SYS_OP_MAP_NONNULL(D1.c6) and SYS_OP_MAP_NONNULL(D2.c14) = SYS_OP_MAP_NONNULL(D1.c7)) inner join SACOMMON3694452 D3 On SYS_OP_MAP_NONNULL(D3.c15) = SYS_OP_MAP_NONNULL(D1.c2) and D3.c12 = D1.c5 and D3.c11 = D1.c4 and SYS_OP_MAP_NONNULL(D3.c16) = SYS_OP_MAP_NONNULL(D1.c8) and SYS_OP_MAP_NONNULL(D3.c13) = SYS_OP_MAP_NONNULL(D1.c6)) inner join SAWITH1 D4 On D4.c10 = D1.c5 and D4.c9 = D1.c4 and SYS_OP_MAP_NONNULL(D4.c11) = SYS_OP_MAP_NONNULL(D1.c6) and SYS_OP_MAP_NONNULL(D4.c12) = SYS_OP_MAP_NONNULL(D1.c7) where (D2.c17 = 1 and D3.c17 = 16))select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9, D1.c10 as c10 from (select 0 as c1, D1.c3 as c2, D1.c4 as c3, '经营利润率' as c4, D1.c8 as c5, case when D1.c9 <= '2013-10' then case when D1.c5 = 0 then 0 else cast(D1.c6 as DOUBLE PRECISION) / nullif(D1.c5, 0) * 100.0 end else NULL end as c6, case when D1.c9 <= '2013-10' then case when D1.c1 = 0 then 0 else cast(D1.c2 as DOUBLE PRECISION) / nullif(D1.c1, 0) * 100.0 end else NULL end as c7, D1.c10 as c8, D1.c11 as c9, D1.c12 as c10, ROW_NUMBER() OVER(PARTITION BY D1.c3, D1.c4, D1.c8, D1.c10, D1.c11, D1.c12 ORDER BY D1.c3 ASC, D1.c4 ASC, D1.c8 ASC, D1.c10 ASC, D1.c11 ASC, D1.c12 ASC) as c11 from SAWITH2 D1) D1 where (D1.c11 = 1);WITH T_A AS (SELECT CHAIN_T_A_SK, HEADER_ID, OFFICE_CODE, T_A_CODE, T_A_SHORT_NAME, T_A_NAME, T_A_ADDRESS, T_A_PHONE, T_A_PROPERITY, COMPANY_PROPERTY, T_A_AREA, PROVINCE_SEQ, T_A_PROVINCE, CITY_SEQ, T_A_CITY, T_A_COUNTY, T_A_COOPER, T_A_BRAND, T_A_NATURE, LEASE_BUILDING_AREA, CONS_BUILDING_AREA, ROOM_QUANTITY, SEATS_QUANTITY, MEAL_QUANTITY, BUSINESS_LICENSE_DATE, SIGN_DATE, START_DATE, END_DATE, case when SIGN_DATE >= PRE_BUSINESS_DATE then SIGN_DATE else PRE_BUSINESS_DATE end PRE_BUSINESS_DATE, BUSINESS_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, T_A_SEGMENT, T_A_AREA_1, T_A_TYPE, ORG_CODE, BUSINESS_LICENSE_END_DATE, PMS_CODE, ORG_ID, PRO_HANDOVER_DATE, FOREIGN_RENT_AREA, SIGN_YEAR, COMPANY_NAME, REMOVE_DATE, EFFECTIVE_DATE, EXPIRATION_DATE, CURRENT_FLAG, ETL_DATE, OLD_T_A_NAME, BRAND_CODE, EQUITY_NAME, (EQUITY_RATE / 100.00) AS EQUITY_RATE, nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE, floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN, floor(business_date - pre_business_date) AS DIFF_B_PRE, floor(business_date - sign_date) AS DIFF_B_SIGN, CASE WHEN TERMINATION_DATE IS NULL THEN 'N' ELSE 'Y' END IS_FLAG, TERMINATION_DATE, to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act, to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act, to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt, to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt FROM T_A, (select t.T_A_code u_T_A_code, max(t.effective_date) u_effective_date from T_A t group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t WHERE T_A.T_A_code = t.u_T_A_code and T_A.effective_date = t.u_effective_date),SAWITH0 AS (select distinct T_A.T_A_SHORT_NAME as c1, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2, T_B.CAL_MONTH_NAME as c3, T_A.EFFECTIVE_MONTH_ACT as c4, T_A.EFFECTIVE_MONTH_BGT as c5, T_A.T_A_CODE as c6, T_A.T_A_COOPER as c7, T_B.CAL_MONTH_ID as c8 from T_B T_B /* Dim-T_B */, T_C T_C /* Fact-T_C_Act */, T_A where (T_C.T_A_CODE = T_A.T_A_CODE and T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and T_B.CAL_YEAR_ID = '2013' and T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and T_C.PERIOD_MON >= '2013-05' and (substr(T_C.T_A_CODE, 1, 1) in ('A', 'B')) and (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >= T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and T_B.CAL_MONTH_NAME >= '2013-05')),SACOMMON3694452 AS (select sum(T_C.AVHIRE_QTY_ACT) as c9, sum(T_C.RMHIRE_QTY_ACT) as c10, T_A.EFFECTIVE_MONTH_ACT as c11, T_A.EFFECTIVE_MONTH_BGT as c12, T_A.T_A_CODE as c13, T_A.T_A_COOPER as c14, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c15, T_B.CAL_MONTH_ID as c16, grouping_id(T_A.T_A_CODE, T_A.T_A_COOPER, T_A.EFFECTIVE_MONTH_ACT, T_A.EFFECTIVE_MONTH_BGT, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_B.CAL_MONTH_ID) as c17 from T_B T_B /* Dim-T_B */, T_C T_C /* Fact-T_C_Act */, T_A where (T_B.CAL_MONTH_NAME < T_A.EXPIRATION_MONTH_ACT and T_B.CAL_YEAR_ID = '2013' and T_C.T_A_CODE = T_A.T_A_CODE and T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and T_B.CAL_MONTH_NAME >= T_A.EFFECTIVE_MONTH_ACT and T_B.CAL_MONTH_NAME >= '2013-05' and (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >= T_B.CAL_MONTH_NAME or T_A.TERMINATION_DATE is null)) group by grouping sets((cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_COOPER, T_A.T_A_CODE),(T_B.CAL_MONTH_ID, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_CODE))),SAWITH1 AS (select sum(T_C.AVHIRE_QTY_ACT) as c7, sum(T_C.RMHIRE_QTY_ACT) as c8, T_A.EFFECTIVE_MONTH_ACT as c9, T_A.EFFECTIVE_MONTH_BGT as c10, T_A.T_A_CODE as c11, T_A.T_A_COOPER as c12 from T_B T_B /* Dim-T_B */, T_C T_C /* Fact-T_C_Act */, T_A where (T_C.T_A_CODE = T_A.T_A_CODE and T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and T_B.CAL_YEAR_ID = '2013' and T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >= T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and T_C.PERIOD_MON >= '2013-05' and T_B.CAL_MONTH_NAME >= '2013-05') group by T_A.T_A_CODE, T_A.T_A_COOPER, T_A.EFFECTIVE_MONTH_ACT, T_A.EFFECTIVE_MONTH_BGT),SAWITH2 AS (select D4.c7 as c1, D4.c8 as c2, D1.c7 as c3, D1.c1 as c4, D2.c9 as c5, D2.c10 as c6, D3.c10 as c7, D1.c2 as c8, D1.c3 as c9, D1.c4 as c10, D1.c5 as c11, D1.c6 as c12 from ((SAWITH0 D1 inner join SACOMMON3694452 D2 On SYS_OP_MAP_NONNULL(D2.c15) = SYS_OP_MAP_NONNULL(D1.c2) and D2.c12 = D1.c5 and D2.c11 = D1.c4 and SYS_OP_MAP_NONNULL(D2.c13) = SYS_OP_MAP_NONNULL(D1.c6) and SYS_OP_MAP_NONNULL(D2.c14) = SYS_OP_MAP_NONNULL(D1.c7)) inner join SACOMMON3694452 D3 On SYS_OP_MAP_NONNULL(D3.c15) = SYS_OP_MAP_NONNULL(D1.c2) and D3.c12 = D1.c5 and D3.c11 = D1.c4 and SYS_OP_MAP_NONNULL(D3.c16) = SYS_OP_MAP_NONNULL(D1.c8) and SYS_OP_MAP_NONNULL(D3.c13) = SYS_OP_MAP_NONNULL(D1.c6)) inner join SAWITH1 D4 On D4.c10 = D1.c5 and D4.c9 = D1.c4 and SYS_OP_MAP_NONNULL(D4.c11) = SYS_OP_MAP_NONNULL(D1.c6) and SYS_OP_MAP_NONNULL(D4.c12) = SYS_OP_MAP_NONNULL(D1.c7) where (D2.c17 = 1 and D3.c17 = 16))select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9, D1.c10 as c10 from (select 0 as c1, D1.c3 as c2, D1.c4 as c3, '经营利润率' as c4, D1.c8 as c5, case when D1.c9 <= '2013-10' then case when D1.c5 = 0 then 0 else cast(D1.c6 as DOUBLE PRECISION) / nullif(D1.c5, 0) * 100.0 end else NULL end as c6, case when D1.c9 <= '2013-10' then case when D1.c1 = 0 then 0 else cast(D1.c2 as DOUBLE PRECISION) / nullif(D1.c1, 0) * 100.0 end else NULL end as c7, D1.c10 as c8, D1.c11 as c9, D1.c12 as c10, ROW_NUMBER() OVER(PARTITION BY D1.c3, D1.c4, D1.c8, D1.c10, D1.c11, D1.c12 ORDER BY D1.c3 ASC, D1.c4 ASC, D1.c8 ASC, D1.c10 ASC, D1.c11 ASC, D1.c12 ASC) as c11 from SAWITH2 D1) D1 where (D1.c11 = 1);改写后网友反馈 有教无类 18:40:09
起作用了没
2013-11-09
11:05:23
起作用了
11:05:33
0.8秒
起作用了没
2013-11-0911:05:23
起作用了
11:05:33
0.8秒
- 用with改写优化sql
- 用with改写优化sql之二
- 改写SQL优化SQL
- SQL改写优化技巧
- 利用WITH AS改写SQL
- 用ROLLUP改写sql
- 用ROLLUP改写sql
- 改写SQL语句优化MySQL性能
- SQLServer性能优化之改写SQL语句
- SQL优化-标量子查询的改写
- 优化案例 | CASE WHEN进行SQL改写优化
- SQL优化之基于SQL特征的改写
- 标量子查询SQL改写一则（包括WITH的改写）
- oracle with sql优化
- 用MERGE改写UPDATE的优化
- 一个非常不友好的SQL的优化改写
- 使用with as 优化SQL
- 使用WITH AS 优化SQL
- Android Wifi的一些属性
- Matlab之graythresh()函数详解
- 回调函数
- C语言数组实现栈的基本操作，并利用O(1)求出栈中最小元素
- JAVA系列-设计模式-解释器模式
- 用with改写优化sql
- 分治算法-最近点对问题、大整数相乘
- kernel list 与普通list区别及其最牛叉的地方
- 【百度地图】计算两组经纬度坐标之间的距离PHP函数
- 6年开发的一些体会
- AJAX请求php写COOKIE不生效的原因
- 数据结构之图的存储结构_前向星
- NSArray NSSet NSDictionary 元素取出方法
- ubuntu华硕Z87-plus主板网卡驱动
