orcale优化统计以及占位操作、统计函数的应用(2)

 SELECT CASE CLQX
         WHEN 0 THEN
          'A'
         WHEN 1 THEN
          'B'
         WHEN 2 THEN
          'C'
         WHEN 3 THEN
          'D'
         WHEN 4 THEN
          'E'
         ELSE
          ''
       END LXMC,
       '刷子' AS TJDW,
       '2010-12-27' AS STARTDATE,
       '2011-10-29' AS ENDDATE,
       '陈伟川' AS CZYXM,
       ROWNUM AS YY,
       G.*
  from (SELECT clqx,
               SUM(KC) AS KC,
               SUM(K) AS KCWP,
               SUM(HC) AS HC,
               SUM(H) AS HCWP,
               SUM(MC) AS MTC,
               SUM(M) AS MTCWP,
               SUM(NC) AS NYC,
               SUM(N) AS NYWP,
               SUM(TC) AS TLJ,
               SUM(T) AS TLJWP,
               SUM(FC) AS FJDC,
               SUM(F) AS FJDCWP,
               SUM(JC) AS JC,
               SUM(J) AS JCWP,
               SUM(QC) AS QT,
               SUM(Q) AS QTWP,
               SUM(JC) + SUM(KC) + SUM(HC) + SUM(MC) + SUM(NC) + SUM(TC) +
               SUM(FC) + SUM(QC) AS HJ,
               SUM(K) + SUM(H) + SUM(M) + SUM(N) + SUM(T) + SUM(F) + SUM(Q) +
               SUM(J) AS HJWP
          FROM (SELECT CLQX,
                       E.UNITSID,
                       CASE
                         WHEN E.CLLB = 'F' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS FC,
                       CASE
                         WHEN E.CLLB = 'F' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'F' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS F,
                       CASE
                         WHEN E.CLLB = 'H' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS HC,
                       CASE
                         WHEN E.CLLB = 'H' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'H' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS H,
                       CASE
                         WHEN E.CLLB = 'J' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS JC,
                       CASE
                         WHEN E.CLLB = 'J' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'J' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS J,
                       CASE
                         WHEN E.CLLB = 'K' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS KC,
                       CASE
                         WHEN E.CLLB = 'K' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'K' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS K,
                       CASE
                         WHEN E.CLLB = 'M' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS MC,
                       CASE
                         WHEN E.CLLB = 'M' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'M' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS M,
                       CASE
                         WHEN E.CLLB = 'N' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS NC,
                       CASE
                         WHEN E.CLLB = 'N' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'N' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS N,
                       CASE
                         WHEN E.CLLB = 'Q' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS QC,
                       CASE
                         WHEN E.CLLB = 'Q' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'Q' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS Q,
                       CASE
                         WHEN E.CLLB = 'T' THEN
                          MAX(XH)
                         ELSE
                          0
                       END AS TC,
                       CASE
                         WHEN E.CLLB = 'T' AND MAX(E.SFYP) = 0 THEN
                          MAX(NUM)
                         WHEN E.CLLB = 'T' AND MAX(E.SFYP) = 1 THEN
                          MAX(XH) - MAX(NUM)
                         ELSE
                          0
                       END AS T
                  FROM (SELECT B.CLQX,
                               B.CLLB,
                               B.UNITSID,
                               COUNT(B.CLLB) AS NUM,
                               B.SFYP,

                                --累计总数
                               SUM(COUNT(B.CLLB)) OVER(PARTITION BY B.CLQX, B.CLLB, B.UNITSID) AS XH,

                               --根据要求对重复的数据排序
                               ROW_NUMBER() OVER(PARTITION BY B.CLQX, B.CLLB, B.UNITSID ORDER BY B.CLQX, B.CLLB, B.UNITSID DESC) AS PX
                          FROM W_CARINFO B,
                               (SELECT ROWID AS NUMID
                                  FROM W_CARINFO A
                                 where 1 = 1
                                   AND A.JJDW = '123456789'
                                   AND A.ZKSJ >=
                                       TO_DATE('2010-12-27 00:00',
                                               'YYYY-MM-DD HH24:MI')
                                   AND A.ZKSJ <=
                                       TO_DATE('2011-10-29 23:59',
                                               'YYYY-MM-DD HH24:MI')
                                   AND A.ZKLX = '01'
                                   AND A.UNITSID is not null) C
                         WHERE B.ROWID = C.NUMID
                         GROUP BY B.CLQX, B.CLLB, B.SFYP, B.UNITSID) E

                  --只取第一条数据
                 WHERE E.PX = 1
                 GROUP BY CLQX, CLLB, UNITSID) F
         GROUP BY F.CLQX) G
 ORDER BY G.CLQX ASC