sql按层级汇总

with temp as
 (select a.name as coaname,
         a.code,
         a.parent_code,
         a.is_leaf,
         b.pk_rm_prd,
         b.prdname,
         b.acct_prd,
         b.prin_pmt
    from (select pk_coa, name, code, parent_code, is_leaf
            from fc_map_coa
           where nvl(dr, 0) = 0
          --and pk_org = parameter('pk_org')
          --and pk_coasch = parameter('coasch')
          ) a
    left join (select a.*, b.name as prdname, b.acct_prd
                from (select pk_rm_coa,
                             pk_prd as pk_rm_prd,
                             sum(prin_pmt) as prin_pmt
                        from (select (select t.pk_prd
                                        from (select pk_prd,
                                                     (case prd_unit
                                                       when 0 then
                                                        to_char(to_date('2011-06-15',
                                                                        'yyyy-MM-dd') +
                                                                prd_val,
                                                                'yyyy-MM-dd')
                                                       when 1 then
                                                        to_char(add_months(to_date('2011-06-15',
                                                                                   'yyyy-MM-dd'),
                                                                           prd_val),
                                                                'yyyy-MM-dd')
                                                       when 2 then
                                                        to_char(add_months(to_date('2011-06-15',
                                                                                   'yyyy-MM-dd'),
                                                                           prd_val * 12),
                                                                'yyyy-MM-dd')
                                                     end) prd_date
                                                from fc_map_prd
                                               where nvl(dr, 0) = 0
                                              -- and pk_prd_sch = '1001S310000000001UT6'
                                               order by acct_prd) t
                                       where rownum = 1
                                         and t.prd_date >=
                                             to_char(to_date('2011-06-15',
                                                             'yyyy-MM-dd') +
                                                     a.days,
                                                     'yyyy-MM-dd')) as pk_prd,
                                     a.*
                                from cfe_alm_res_pmt a
                               where nvl(a.dr, 0) = 0
                                 and a.data_date = '2011-06-15'
                              --and a.pk_coa_set = parameter('coasch')
                              --and a.currency = parameter('currency')
                              --and a.branch_code = parameter('pk_org')
                              )
                       group by pk_coa_set,
                                pk_rm_coa,
                                pk_prd,
                                branch_code,
                                currency,
                                asst_liab) a
                left join fc_map_prd b
                  on a.pk_rm_prd = b.pk_prd
                 and nvl(b.dr, 0) = 0
              --and b.pk_prd_sch = '1001S310000000001UT6'
              ) b
      on a.pk_coa = b.pk_rm_coa),
      tempAcct as (select a.coaname,
         a.code,
         a.parent_code,
         a.is_leaf,
         a.pk_rm_prd,
         b.prdname,
         b.acct_prd from (select * from temp where is_leaf = 'N') a left join (select distinct acct_prd,prdname from  temp where is_leaf = 'Y') b on 1=1  )


select coaname, code, prdname, acct_prd, prin_pmt
  from temp
union all
select * from (select '合计' as coaname,
         t.code,
        
         t.prdname,
         t.acct_prd,        
       (select sum(nvl(prin_pmt,0)) 
          from temp where acct_prd=t.acct_prd
        connect by parent_code = prior code
         start with parent_code = t.code ) as prin_pmt
  from tempAcct t
 order by t.code)

特殊情境下的按层级汇总

select pk_org, pk_fatherorg, innercode
  from org_orgs
 where dr = 0
   and innercode like
       (select concat(innercode, '%')--前提是innercode是按照一定规则编码，01是目录，01下所有的包括目录和子节点的都以01开头
          from org_orgs
         where pk_org = '00011110000000000PKL')