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

来源：互联网发布：mac地址烧录1.36 编辑：程序博客网时间：2024/04/30 10:43

--最后一步把多行的数据整成需要的一列数据
select f.zkyx,sum(FC) AS FC ,sum(F) AS F,sum(HC) as HC,
       sum(H) as H,sum(JC) AS JC,sum(J) as J,sum(KC) as KC
       ,sum(K) as K,sum(MC) as MC ,sum(M) AS M,sum(NC) AS nC
       ,sum(N) as N,sum(QC) as QC,sum(Q) as Q,sum(TC) as TC,sum(T) as T
       ,sum(FC)+sum(HC)+sum(JC)+sum(KC)+sum(MC)
       +sum(NC)+sum(QC)+sum(TC) as zs
from (select zkyx, ---按所属类型统计(生成的是多行零散的数据)
        case when e.cllb='F' then xh else 0 end as FC,
        case when e.cllb='F' then num else 0 end as F,
        case when e.cllb='H' then xh else 0 end as HC,
        case when e.cllb='H' then num else 0 end as H,
        case when e.cllb='J' then xh else 0 end as JC,
        case when e.cllb='J' then num else 0 end as J,
        case when e.cllb='K' then xh else 0 end as KC,
        case when e.cllb='K' then num else 0 end as K,
        case when e.cllb='M' then xh else 0 end as MC,
        case when e.cllb='M' then num else 0 end as M,
        case when e.cllb='N' then xh else 0 end as NC,
        case when e.cllb='N' then num else 0 end as N,
        case when e.cllb='Q' then xh else 0 end as QC,
        case when e.cllb='Q' then num else 0 end as Q,
        case when e.cllb='T' then xh else 0 end as TC,
        case when e.cllb='T' then num else 0 end as T
From (select b.zkyx,
       b.cllb,
       count(b.cllb) AS num,
       b.sfyp,
       --计算总数
       sum(count(b.cllb)) over( partition by b.zkyx,b.cllb ) as xh
from w_Carinfo b, (select Rowid as numID from w_Carinfo a) c
where b.rowid = c.numID
group by b.zkyx, b.cllb, b.sfyp) e where e.sfyp=0 ) f
group by f.zkyx

select g.* From (select e.*,rownum num
from (select decode(c.lrrdw,null,'000000',c.lrrdw) lrrdw,
               nvl(sum(c.jcz + c.jsq + c.jdw + c.jxx), 0) ct,
               nvl(sum(c.jcz), 0) jcz,
               nvl(sum(c.jsq), 0) jsq,
               nvl(sum(c.jdw), 0) jdw,
               nvl(sum(c.jxx), 0) jxx
          From (select b.lrrdw,
                       decode(b.xccs, 26, b.xccsnum, 0) jcz,
                       decode(b.xccs, 25, b.xccsnum, 0) jsq,
                       decode(b.xccs, 23, b.xccsnum, 0) jdw,
                       decode(b.xccs, 24, b.xccsnum, 0) jxx
                  From (select substr(a.lrrdw, 0, 4) || '0000' lrrdw,
                               a.xccs,
                               count(id) xccsNum
                          From xc_fiveenterwork a
                         where a.xccs in (23, 24, 25, 26)
                         group by substr(a.lrrdw, 0, 4), a.xccs) b) c
         group by cube (c.lrrdw) order by lrrdw ) e ) g where g.num <>2

select e.*, rownum pm
from (select c.dwjc,
               nvl(sum(c.jcz + c.jsq + c.jdw + c.jxx), 0) ct,
               nvl(sum(c.jcz), 0) jcz,
               nvl(sum(c.jsq), 0) jsq,
               nvl(sum(c.jdw), 0) jdw,
               nvl(sum(c.jxx), 0) jxx
          From (select pgex_util_public.getDwmcByDwdm(trim(b.lrrdw), 2) dwjc,
                       decode(b.xccs, 26, b.xccsnum, 0) jcz,
                       decode(b.xccs, 25, b.xccsnum, 0) jsq,
                       decode(b.xccs, 23, b.xccsnum, 0) jdw,
                       decode(b.xccs, 24, b.xccsnum, 0) jxx
                  From (select a.lrrdw, a.xccs, count(id) xccsNum
                          From xc_fiveenterwork_new a
                         where a.lrrdw <> '52000000'
                           and a.xccs in (23, 24, 25, 26)
                         group by a.lrrdw, a.xccs) b) c
         group by c.dwjc) e

红色的为已经分组好地数据，类似于

列名a 列名b 列名c

1 3

1 4

2 5

2 6

蓝色地就类似于把红色已经分类好地数据再进行分类

列名a 列名b 列名c

1 8

2 11