树(三层)结构数据统计

#{pNum}传入树结构code

/*-----三级树结构数据统计(合计)-----*/
with 
/*基础数据准备*/
tp_aj as(
       SELECT gt.pid, gt.type, it.name as pname,gt.pNum pNum, it.deptid,it.status
  FROM (SELECT *
          FROM (SELECT g.updatime，g.obj_id pid，g.pNum，g.type, row_number() over(partition by pNum order by updatime desc) rm
                  FROM GPS_LAST g
                 WHERE 1=1
                 )
         WHERE rm = 1) gt,
       (SELECT name, pNum, type, deptId, pdid, status
          FROM (SELECT i.name,
                       i.pNum,i.type, i.deptid,  i.pdid, i.status,
                       row_number() over(partition by pNum order by time desc) rm
                  FROM REPORT_INFO i)
         WHERE rm = 1) it
 WHERE gt.pNum = it.pNum AND (it.status = '01' OR it.status = '04')  
),

/*---------------------类型分析统计----------------------*/
tp_dtata_g1 as (
     select
          a.deptid dm,
         /*类型1筛选数据-t0*/ count(case when a.type='01' and length(a.pNum)=6  then 1 end) mj,
         /*类型2筛选数据-t1*/ count(case when a.type='01' and length(a.pNum)>6  then 1 end) fj
        /*.......repeat =>  count(case when a.type='01' and length(a.pNum)>6  then 1 end) fj,..........*/
     from tp_dtata a
    group by a.deptid
)
/*----------------------树结构正式统计----------------------*/
select md.dm "dm",
      decode(md.lvl, 2, md.mc, 1, '合计', md.mc) "mc",
      sum(mj_single) "mj_sum",
      sum(fj_single) "fj_sum"
       /*.......repeat => sum(fj_single) "fj_sum"  ..........*/
      from
        (
        select d.deptid dm,d.alias mc,d.sortnum sort_by,level lvl,
          case when connect_by_isleaf = 1 then nvl(g1.mj, 0) when level = 1 then sum(nvl(g1.mj,0)) over() when level = 2 then sum(nvl(g1.mj, 0)) over(partition by decode(level, 2, d.deptid,d.parentid)) end mj_single ,
          case when connect_by_isleaf = 1 then nvl(g1.fj, 0) when level = 1 then sum(nvl(g1.fj,0)) over() when level = 2 then sum(nvl(g1.fj, 0)) over(partition by decode(level, 2, d.deptid,d.parentid)) end fj_single         
          /*.......repeat =>   case when connect_by_isleaf = 1 then nvl(g1.fj, 0) when level = 1 then sum(nvl(g1.fj,0)) over() when level = 2 then sum(nvl(g1.fj, 0)) over(partition by decode(level, 2, d.deptid,d.parentid)) end fj_single,   ..........*/
          from  region_info d
          left join tp_dtata_g1 g1 on g1.dm = d.deptid
        start with d.deptid = nvl(#{pNum}, '320500000000')
        connect by prior d.deptid = d.parentid
        )
        md
where 1 = 1 and md.lvl in (1, 2)
group by md.dm, decode(md.lvl, 2, md.mc, 1, '合计', md.mc), md.sort_by
order by length(md.dm) desc, md.sort_by