--最后一步把多行的数据整成需要的一列数据
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