Oracle的递归汇总让我头疼

郁绣梓

初衷是做一个关于行政区划的递归汇总，但加上按阶段类别分类汇总的条件就感到很棘手，目前在iBatis里只好这么写

<select id="getRecordsByCondition" parameterClass ="java.util.Map" resultMap="Stats-result">
<dynamic>
<isEqual property="LEVEL" compareValue="1">
<![CDATA[
select * from (
select t.xzqhdm,'该地直辖' as xzqh,t.hs,t.fwmj,t.lgmsl,t.fssssl,t.rk,t.tdmj,t.jdlb
from YM_MV_NCYMCC t
]]>
<isNotEmpty prepend=' where ' property='PXZQHDM'>               
<![CDATA[t.xzqhdm=#PXZQHDM:VARCHAR#]]>
</isNotEmpty>
<isNotEmpty prepend=' and ' property='JDLB'>               
<![CDATA[t.jdlb=#JDLB:CHAR#]]>
</isNotEmpty>
<![CDATA[
union all
select t.xzqhdm,t.xzqh,
(select sum(hs)
from YM_MV_NCYMCC t2
start with t.xzqhdm=t2.xzqhdm and t2.jdlb=#JDLB#
connect by prior xzqhdm=pxzqhdm and t2.jdlb=#JDLB#
) hs,
(select sum(fwmj)
from YM_MV_NCYMCC t2
start with t.xzqhdm=t2.xzqhdm and t2.jdlb=#JDLB#
connect by prior xzqhdm=pxzqhdm and t2.jdlb=#JDLB#
) fwmj,
(select sum(lgmsl)
from YM_MV_NCYMCC t2
start with t.xzqhdm=t2.xzqhdm and t2.jdlb=#JDLB#
connect by prior xzqhdm=pxzqhdm and t2.jdlb=#JDLB#
) lgmsl,
(select sum(fssssl)
from YM_MV_NCYMCC t2
start with t.xzqhdm=t2.xzqhdm and t2.jdlb=#JDLB#
connect by prior xzqhdm=pxzqhdm and t2.jdlb=#JDLB#
) fssssl,
(select sum(rk)
from YM_MV_NCYMCC t2
start with t.xzqhdm=t2.xzqhdm and t2.jdlb=#JDLB#
connect by prior xzqhdm=pxzqhdm and t2.jdlb=#JDLB#
) rk,     
(select sum(tdmj)
from YM_MV_NCYMCC t2
start with t.xzqhdm=t2.xzqhdm and t2.jdlb=#JDLB#
connect by prior xzqhdm=pxzqhdm and t2.jdlb=#JDLB#
) tdmj,
t.jdlb
from YM_MV_NCYMCC t
]]>
<isNotEmpty prepend=' where ' property='XZQHDM'>               
<![CDATA[t.XZQHDM like #XZQHDM:VARCHAR#]]>
</isNotEmpty>
<isNotEmpty prepend='and' property='PXZQHDM'>               
<![CDATA[t.XZQHDM<>#PXZQHDM:VARCHAR#]]>
</isNotEmpty>
<isNotEmpty prepend=' and ' property='JDLB'>               
<![CDATA[t.jdlb=#JDLB:CHAR#]]>
</isNotEmpty>
<![CDATA[
) v
]]>
<isNotEmpty prepend='and' property='QUERYVAL'>               
<![CDATA[v.$FIELD$$operator$#QUERYVAL:NUMBER#]]>
</isNotEmpty>
</isEqual>
</dynamic>   
</select>
这个同样的递归执行了几遍，逻辑应该可以优化下，可我脑袋已经很难再转动了...
做点小结:
Select * from …. Where [结果过滤条件语句]
Start with  [and起始条件过滤语句]
Connect by prior [and中间记录过滤条件语句]
connect by prior xzqhdm=pxzqhdm 采用的是从根往叶汇总的方式