oracle 树形分组小计sql

[size=small]

select mlk_flag_2013.ID_, qnsj_
  from (select case grouping(t.levels) when 1 then '小计' else to_char(ltrim(regexp_substr(t.levels, ';[^;]*$', 1, 1), ';')) end ID_,
               case grouping(t.levels) when 1 then ';小计' else to_char(t.levels) end levels
          from (select frm, sys_connect_by_path(frm, ';') levels from mlk_flag_2013 where time_ = '201312'
                 start with pid_ = '103068526' connect by prior frm = pid_) t
         group by rollup(t.levels)) mlk_flag_2013
  left join (select case grouping(t.level1) when 1 then '小计' else to_char(t.level1) end level1,
                    t.level2,
                    t.level3,
                    sum(t_jh_cpxssr.qnsj_) qnsj_
               from (select * from t_jh_cpxssr where time_ = '2013') t_jh_cpxssr
               left join (select frm,
							ltrim(regexp_substr(sys_connect_by_path(frm,';'),';[^;]*',1,1),';') level1,
							ltrim(regexp_substr(sys_connect_by_path(frm,';'),';[^;]*',1,2),';') level2,
							ltrim(regexp_substr(sys_connect_by_path(frm,';'),';[^;]*',1,3),';') level3
                           from mlk_flag_2013 where time_ = '201312'
                          start with pid_ = '103068526' connect by prior frm = pid_) t on t_jh_cpxssr.comp_code_ = t.frm
              group by rollup(t.level1, t.level2, t.level3)) SUM_2013 on mlk_flag_2013.levels =
                    rtrim(';' || SUM_2013.level1 || ';' || SUM_2013.level2 || ';' || SUM_2013.level3, ';')

[/size]