标量子查询改写

SELECT A.CONS_NO,

       A.CONS_NAME,

       (SELECT SUM(T.PURCH_PQ)

          FROMGAS_CA.A_CARD_METER_PAY T

         WHERE CONS_NO = A.CONS_NO

           AND T.PURCH_GAS_TIME > TO_DATE('20160229', 'YYYYMMDD')) 购气量

  FROM GAS_MB.C_CONS A

 WHERE A.ORG_NO LIKE '1125%'

   AND EXISTS

 (SELECT 1

          FROM GAS_MB.C_MP B

         WHERE B.CONS_ID = A.CONS_ID

           AND EXISTS

         (SELECT 1

                  FROM GAS_MB.C_METER

                 WHERE MP_ID = B.MP_ID

                   AND PRODUCE_FACT IN ('0010', '0015', '0024')));

 在子查询的数量比较大的时候查询效率会比较低下，这里用表连接的方式改写一下

SELECT *

  FROM (SELECT A.CONS_NO, A.CONS_NAME, B.购气量

          FROM GAS_MB.C_CONS A,

               (SELECT B1.CONS_NO, SUM(B1.PURCH_PQ) 购气量

                  FROMGAS_CA.A_CARD_METER_PAY B1

                 WHERE B1.ORG_NO LIKE '1125%'

                   AND B1.PURCH_GAS_TIME > TO_DATE('20160229', 'YYYYMMDD')

                 GROUP BY B1.CONS_NO) B

         WHERE A.ORG_NO LIKE '1125%'

           AND A.CONS_NO = B.CONS_NO(+)

           AND EXISTS

         (SELECT 1

                  FROM GAS_MB.C_MP B

                 WHERE B.CONS_ID = A.CONS_ID

                   AND EXISTS

                 (SELECT 1

                          FROM GAS_MB.C_METER

                         WHERE MP_ID = B.MP_ID

                           AND PRODUCE_FACT IN ('0010', '0015', '0024'))))

 WHERE 购气量 IS NOT NULL;