筛选减小开销优化

SELECT 0                        AS ID,
       T1.CUSTOMERNAME          AS CUSTOMERNAME,
       T.CUSTOMERLEVEL          AS CUSTOMERLEVEL,
       '续保招揽' AS               BIZTYPENAME,
       T2.FULLNAME              AS FULLNAME,
       T.NEXTTRACKDATE          AS PLANTIME,
       T.INSURANCEMATURITYDATE  AS INSURANCEMATURITYDATE,
       T.COMMERCIALENDDATE      AS COMMERCIALENDDATE,
       T3.STATUSCODEDATE10      AS STATUSCODEDATE10,
       T4.FULLNAME              AS UNDERTAKER10
FROM   RT_CUSTALLOCRESULT T
       LEFT JOIN UM_CUSTOMER T1
            ON  T1.CUSTOMERCODE = T.CUSTOMERNO
       LEFT JOIN CM_STAFF T2
            ON  T2.STAFFPKID = T.SALEID
       LEFT JOIN (
                SELECT T.FRAMENO,
                       T.UNDERTAKER10,
                       T.STATUSCODEDATE10
                FROM   (
                           SELECT T1.FRAMENO,
                                  T1.UNDERTAKER10,
                                  T1.STATUSCODEDATE10,
                                  T1.REPAIRNO,
                                  MAX(T1.REPAIRNO) OVER(PARTITION BY T1.FRAMENO) AS 
                                  MAXREPAIRNO
                           FROM   RT_REPAIR T1
                           WHERE  T1.STATUSCODEDATE10 IS NOT NULL
                           ORDER BY
                                  T1.FRAMENO,
                                  T1.STATUSCODEDATE10
                       ) T
                WHERE  REPAIRNO = MAXREPAIRNO
            ) T3
            ON  T3.FRAMENO = T.FRAMENO
       LEFT JOIN CM_STAFF T4
            ON  T4.STAFFPKID = T3.UNDERTAKER10
WHERE  VALUE(T.DELETED, '0') = '0'
       AND (
               (
                   T.INSURANCEMATURITYDATE BETWEEN '2015-01-21' AND '2015-02-05'
               )
               OR (T.COMMERCIALENDDATE BETWEEN '2015-01-21' AND '2015-02-05')
           )
       AND T.STATUS <> '99'
       AND T.STATUS <> '05'      
       

 

筛选减小开销优化

SELECT *
FROM   (
           SELECT T1.FRAMENO,
                  T1.UNDERTAKER10,
                  T1.STATUSCODEDATE10,
                  T1.REPAIRNO,
                  MAX(T1.REPAIRNO) OVER(PARTITION BY T1.FRAMENO) AS MAXREPAIRNO
           FROM   RT_REPAIR T1
           WHERE  T1.STATUSCODEDATE10 IS NOT NULL
           ORDER BY
                  T1.FRAMENO,
                  T1.STATUSCODEDATE10
       ) T
WHERE  REPAIRNO = MAXREPAIRNO