oracle 树形分组小计sql

eicsguepdz

[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, ';')