实际项目（报表功能）遇到的sql优化（存储过程、临时表和with的选择）

2019独角兽企业重金招聘Python工程师标准>>>

    因为报表要从别的表加载数据，由于表很多所以设计出来的SQL及其复杂。下面就是初始设计出来的SQL：

SELECT E.*, comp.COMPANY_NAME FROM AU_COMPANY comp, (
    SELECT SUM(points.ORDER_AMOUNT) QUANCUN_PRICE_SUM, COUNT(points.ID) QUANCUN_AMOUNT_SUM, points.SETTLE_COMP_ID COMPANY_ID, substr(points.ORDER_DATE,4,8) ORDER_DATE FROM ECP_CUSTOMER_POINTS_ORDER points inner join (        
        SELECT comp.ID FROM AU_COMPANY comp INNER JOIN (
            SELECT distinct REL.PARTYID from (SELECT distinct C.ID FROM AU_PARTYRELATION REL, AU_PARTYTYPE TYP, AU_COMPANY C WHERE REL.PARTYTYPE_ID =TYP.ID AND TYPE_LEVEL='2' AND TYP.NAME  ='公司' AND C.ID=REL.PARTYID AND C.ISFOODCARD='1') A, AU_PARTYRELATION REL, AU_PARTYTYPE TYP WHERE REL.PARTYTYPE_ID =TYP.ID AND TYP.NAME  ='公司' START WITH REL.PARTYID = A.ID CONNECT BY PRIOR REL.PARTYID =REL.PARENT_PARTYID
        ) B ON comp.SETTLE_UNIT_ID=B.PARTYID AND comp.IS_SETTLEMENT_UNIT ='1'         
    ) D ON points.SETTLE_COMP_ID = D.ID WHERE points.STATUS='3' group by points.SETTLE_COMP_ID, substr(points.ORDER_DATE,4,8)
) E WHERE E.COMPANY_ID=comp.ID 
union SELECT 0 QUANCUN_PRICE_SUM, 0 QUANCUN_AMOUNT_SUM, F.COLUMN2 COMPANY_ID,  substr(sysdate,4,8) ORDER_DATE, F.COMPANY_NAME  COMPANY_NAME FROM (
    SELECT comp3.* FROM AU_COMPANY comp3 INNER JOIN (
        SELECT distinct REL.PARTYID FROM (SELECT distinct C.ID FROM AU_PARTYRELATION REL, AU_PARTYTYPE TYP, AU_COMPANY C WHERE REL.PARTYTYPE_ID =TYP.ID AND TYPE_LEVEL='2' AND TYP.NAME  ='公司' AND C.ID=REL.PARTYID AND C.ISFOODCARD='1') A, AU_PARTYRELATION REL, AU_PARTYTYPE TYP WHERE REL.PARTYTYPE_ID =TYP.ID AND TYP.NAME  ='公司' START WITH REL.PARTYID = A.ID CONNECT BY PRIOR REL.PARTYID =REL.PARENT_PARTYID
    ) F ON comp3.SETTLE_UNIT_ID=F.PARTYID AND comp3.IS_SETTLEMENT_UNIT ='1'
) F WHERE F.ID NOT IN (
    SELECT distinct comp2.SETTLE_UNIT_ID from AU_COMPANY comp2,ECP_CUSTOMER_POINTS_ORDER points3 where comp2.SETTLE_UNIT_ID=points3.SETTLE_COMP_ID and points3.STATUS='3'
)

     仔细再研究一下自己的SQL，可以看出里面有基本上重复的SQL，重复的SQL可以合并到一块，怎么合并呢，用存储过程的话，要先定义存储过程，在使用的时候再call存储过程。要使用临时表的话，要先定义临时表，再insert进去数据。这两者都要DBA的权限，而我们上生产的时候连个select权限都没有。所以最后使用with关键字，使用的时候要注意with放在SQL最前面的话，定义出来的结果集能一直被后面的SQL引用到，写在中间的话由于with不能作为子句，定义也不能被后面的SQL引用到。

     使用Group By的话，只能返回单行，而Oracle 8.1.6之后引入的分析函数over（）可以返回多行，并且可以查询多个列，综上设计出来的SQL如下：

WITH temp2 AS(
     SELECT comp.ID, comp.COMPANY_NAME  FROM AU_COMPANY comp INNER JOIN (
         SELECT distinct REL.PARTYID from (SELECT distinct C.ID FROM AU_PARTYRELATION REL, AU_PARTYTYPE TYP, AU_COMPANY C WHERE REL.PARTYTYPE_ID =TYP.ID AND TYPE_LEVEL='2' AND TYP.NAME  ='公司' AND C.ID=REL.PARTYID AND C.ISFOODCARD='1') A, AU_PARTYRELATION REL, AU_PARTYTYPE TYP WHERE REL.PARTYTYPE_ID =TYP.ID AND TYP.NAME  ='公司' START WITH REL.PARTYID = A.ID CONNECT BY PRIOR REL.PARTYID =REL.PARENT_PARTYID
     ) B ON comp.SETTLE_UNIT_ID=B.PARTYID AND comp.IS_SETTLEMENT_UNIT ='1'
)SELECT DISTINCT temp3.QUANCUN_AMOUNT_SUM, substr(points.ORDER_DATE,4,8) ORDER_DATE, points.COM_NAME COMPANY_NAME, points.SETTLE_COMP_ID COMPANY_ID, SUM(points.ORDER_AMOUNT) OVER (PARTITION BY points.SETTLE_COMP_ID) QUANCUN_PRICE_SUM FROM ECP_CUSTOMER_POINTS_ORDER points,temp2,(SELECT COUNT(points2.SETTLE_COMP_ID) QUANCUN_AMOUNT_SUM, points2.SETTLE_COMP_ID FROM ECP_CUSTOMER_POINTS_ORDER points2 WHERE 
     points2.STATUS='3' GROUP BY points2.SETTLE_COMP_ID) temp3 WHERE points.STATUS='3'AND points.SETTLE_COMP_ID IS NOT NULL AND points.SETTLE_COMP_ID=temp2.ID AND temp3.SETTLE_COMP_ID=points.SETTLE_COMP_ID AND  substr(points.ORDER_DATE,4,8)= substr(sysdate,4,8)  
         UNION(SELECT 0 QUANCUN_PRICE_SUM, substr(sysdate,4,8) ORDER_DATE, temp2.COMPANY_NAME COMPANY_NAME, temp2.ID COMPANY_ID,  0 QUANCUN_AMOUNT_SUM FROM temp2 WHERE temp2.ID NOT IN(SELECT distinct comp2.SETTLE_UNIT_ID from AU_COMPANY comp2,ECP_CUSTOMER_POINTS_ORDER points3 where comp2.SETTLE_UNIT_ID=points3.SETTLE_COMP_ID and points3.STATUS='3')
)  ORDER BY QUANCUN_PRICE_SUM

 

转载于:https://my.oschina.net/wliming/blog/701381