先说一下什么是递归查询，简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:cid，pid，那么通过表示每一条记录的parent是谁，就可以形成一个树状结构，用上述语法的查询可以取得这棵树的所有记录，其中：
条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR cid = pid；就是说上一条记录的code 是本条记录的pid，即本记录的父亲是上一条记录。
下面是我写的代码：
select daid, XMMC, CSMC, PID, CID, ND, JDUANSL, JDIANSL, WDSL, XMSL, STATUS
from (
--小计
select '' daid,
qt.xtrhzrcs || '小计' xmMC,
qt.xtrhzrcs csmc,
qt.PID,
qt.ZID CID,
qt.ND,
qt.jduansl, --阶段数量
qt.jdiansl,
decode(wd.wdsl, null, 0, wd.wdsl) wdsl,
qt.xmsl,
'3' status
from (select mda.xtrhzrcs, count(ad.filename) wdsl --文档数量
from app_filesupload ad, yw_xmdatzml zml, yw_xmda mda
where 1 = 1
and mda.nd = '2017' --参数
--and mda.xmmc like '%%'--参数
and ad.operation_id = zml.jdid
and zml.daid = mda.daid
group by mda.xtrhzrcs) wd,
--项目数量
(select D.DAID,
D.XMMC,
D.xtrhzrcs,
D.PID,
D.ZID,
D.ND,
D.jduansl, --阶段数量
D.jdiansl,
c.xmsl
from (select a.xtrhzrcs, count(a.xmmc) xmsl
from yw_xmda a
where 1 = 1
and a.nd = '2017' --参数
--and a.xmmc like '%%'--参数
group by a.xtrhzrcs) c,
(select B.DAID,
B.XMMC,
B.xtrhzrcs,
B.PID,
B.ZID,
B.ND,
B.jduansl, --阶段数量
a.jdiansl --阶点数量
from (select da.xtrhzrcs, count(ml.jdianmc) jdiansl --阶点数量
from yw_xmda da,
(select distinct l.jdianmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid
group by da.xtrhzrcs) a,
(select '' daid,
'' xmmc,
da.xtrhzrcs,
'100' PID,
da.xtrhzrcsdm ZID,
da.nd,
count(ml.jduanmc) jduansl --阶段数量
from yw_xmda da,
(select distinct l.jduanmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017'
--and da.xmmc like '%%'
and da.daid = ml.daid
group by da.xmmc,
da.xtrhzrcs,
da.xtrhzrcsdm,
da.nd) b
where A.xtrhzrcs = B.xtrhzrcs) d
where c.xtrhzrcs = d.xtrhzrcs
group by D.XMMC,
D.xtrhzrcs,
D.PID,
D.ZID,
D.ND,
D.jduansl,
D.jdiansl,
c.xmsl) qt
where wd.xtrhzrcs(+) = qt.xtrhzrcs
UNION
--项目
select qt.daid,
qt.xmmc,
qt.xtrhzrcs csmc,
qt.xtrhzrcsdm pid,
qt.daid cID,
qt.nd,
qt.jduansl,
qt.jdiansl,
wd.wdsl,
0 xmsl,
qt.status
from (select a.daid,
a.xmmc,
a.xtrhzrcs,
a.xtrhzrcsdm,
a.nd,
a.jduansl,
jdian.jdiansl,
a.status
from (select xm.daid,
xm.xmmc,
xm.xtrhzrcs,
xm.xtrhzrcsdm,
xm.nd,
jduan.jduansl,
xm.status
from --项目，年度
(select da.daid,
da.xmmc,
da.xtrhzrcs,
da.xtrhzrcsdm,
da.nd,
da.status
from yw_xmda da, yw_xmdatzml ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid(+)
group by da.xtrhzrcs,
da.daid,
da.nd,
da.xmmc,
da.xtrhzrcsdm,
da.status
order by da.xtrhzrcs) xm,
--阶段
(select da.daid,
da.xmmc,
count(ml.jduanmc) jduansl --阶段数量
from yw_xmda da,
(select distinct l.jduanmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid(+)
group by da.daid, da.xmmc) jduan
where xm.daid = jduan.daid) a,
--节点
(select da.daid, da.xmmc, count(ml.jdianmc) jdiansl --阶点数量
from yw_xmda da,
(select distinct l.jdianmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid(+)
group by da.daid, da.xmmc) jdian
where a.daid = jdian.daid) qt,
--文档
(select mda.daid, mda.xmmc, count(ad.filename) wdsl --文档数量
from yw_xmda mda, yw_xmdatzml zml, app_filesupload ad
where
1 = 1
and mda.nd = '2017' --参数
--and mda.xmmc like '%%'--参数
and zml.jdid = ad.operation_id(+)
and mda.daid = zml.daid(+)
group by mda.daid, mda.xmmc) wd
where qt.daid = wd.daid
UNION
--合计
select '' daid,
zs.xmmc,
zs.csmc,
zs.pid,
zs.cid,
zs.nd,
sum(zs.jduansl) jduansl, --阶段总数
sum(zs.jdiansl) jdiansl, --节点总数
sum(zs.wdsl) wdsl, --文档总数
sum(zs.xmsl) xmsl, --项目总数
'3' status
from (select qt.xmmc,
qt.csmc,
qt.pid,
qt.cid,
qt.nd,
qt.jduansl,
qt.jdiansl,
qt.xmsl,
wd.wdsl
from (select mda.xtrhzrcsdm, count(ad.filename) wdsl --文档数量
from app_filesupload ad, yw_xmdatzml zml, yw_xmda mda
where 1 = 1
and mda.nd = '2017'
-- and mda.xmmc like '%%'
and ad.operation_id = zml.jdid
and zml.daid = mda.daid
group by mda.xtrhzrcsdm) wd,
--项目数量
(select d.xtrhzrcsdm,
d.xmmc,
d.csmc,
d.pid,
d.cid,
d.nd,
d.jduansl,
d.jdiansl,
c.xmsl
from (select a.xtrhzrcsdm, count(a.xmmc) xmsl
from yw_xmda a
where 1 = 1
and a.nd = '2017'
-- and a.xmmc like '%%'
group by a.xtrhzrcsdm) c,
(select b.xtrhzrcsdm,
b.xmmc,
b.csmc,
b.pid,
b.cid,
b.nd,
b.jduansl,
a.jdiansl
from (select da.xtrhzrcsdm,
count(ml.jdianmc) jdiansl --阶点数量
from yw_xmda da,
(select distinct l.jdianmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017'
-- and da.xmmc like '%%'
and da.daid = ml.daid
group by da.xtrhzrcsdm) a,
(select '合计' xmmc,
da.xtrhzrcsdm,
'' csmc,
'0' pid,
'100' cid,
da.nd,
count(ml.jduanmc) jduansl --阶段数量
from yw_xmda da,
(select distinct l.jduanmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017'
-- and da.xmmc like '%%'
and da.daid = ml.daid
group by da.nd, da.xtrhzrcsdm) b
where A.xtrhzrcsdm = B.xtrhzrcsdm) d
where c.xtrhzrcsdm = d.xtrhzrcsdm
group by d.xtrhzrcsdm,
d.xmmc,
d.csmc,
d.pid,
d.cid,
d.nd,
d.jduansl,
d.jdiansl,
c.xmsl) qt
where wd.xtrhzrcsdm(+) = qt.xtrhzrcsdm) zs
group by zs.xmmc, zs.csmc, zs.pid, zs.cid, zs.nd) tt
from (
--小计
select '' daid,
qt.xtrhzrcs || '小计' xmMC,
qt.xtrhzrcs csmc,
qt.PID,
qt.ZID CID,
qt.ND,
qt.jduansl, --阶段数量
qt.jdiansl,
decode(wd.wdsl, null, 0, wd.wdsl) wdsl,
qt.xmsl,
'3' status
from (select mda.xtrhzrcs, count(ad.filename) wdsl --文档数量
from app_filesupload ad, yw_xmdatzml zml, yw_xmda mda
where 1 = 1
and mda.nd = '2017' --参数
--and mda.xmmc like '%%'--参数
and ad.operation_id = zml.jdid
and zml.daid = mda.daid
group by mda.xtrhzrcs) wd,
--项目数量
(select D.DAID,
D.XMMC,
D.xtrhzrcs,
D.PID,
D.ZID,
D.ND,
D.jduansl, --阶段数量
D.jdiansl,
c.xmsl
from (select a.xtrhzrcs, count(a.xmmc) xmsl
from yw_xmda a
where 1 = 1
and a.nd = '2017' --参数
--and a.xmmc like '%%'--参数
group by a.xtrhzrcs) c,
(select B.DAID,
B.XMMC,
B.xtrhzrcs,
B.PID,
B.ZID,
B.ND,
B.jduansl, --阶段数量
a.jdiansl --阶点数量
from (select da.xtrhzrcs, count(ml.jdianmc) jdiansl --阶点数量
from yw_xmda da,
(select distinct l.jdianmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid
group by da.xtrhzrcs) a,
(select '' daid,
'' xmmc,
da.xtrhzrcs,
'100' PID,
da.xtrhzrcsdm ZID,
da.nd,
count(ml.jduanmc) jduansl --阶段数量
from yw_xmda da,
(select distinct l.jduanmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017'
--and da.xmmc like '%%'
and da.daid = ml.daid
group by da.xmmc,
da.xtrhzrcs,
da.xtrhzrcsdm,
da.nd) b
where A.xtrhzrcs = B.xtrhzrcs) d
where c.xtrhzrcs = d.xtrhzrcs
group by D.XMMC,
D.xtrhzrcs,
D.PID,
D.ZID,
D.ND,
D.jduansl,
D.jdiansl,
c.xmsl) qt
where wd.xtrhzrcs(+) = qt.xtrhzrcs
UNION
--项目
select qt.daid,
qt.xmmc,
qt.xtrhzrcs csmc,
qt.xtrhzrcsdm pid,
qt.daid cID,
qt.nd,
qt.jduansl,
qt.jdiansl,
wd.wdsl,
0 xmsl,
qt.status
from (select a.daid,
a.xmmc,
a.xtrhzrcs,
a.xtrhzrcsdm,
a.nd,
a.jduansl,
jdian.jdiansl,
a.status
from (select xm.daid,
xm.xmmc,
xm.xtrhzrcs,
xm.xtrhzrcsdm,
xm.nd,
jduan.jduansl,
xm.status
from --项目，年度
(select da.daid,
da.xmmc,
da.xtrhzrcs,
da.xtrhzrcsdm,
da.nd,
da.status
from yw_xmda da, yw_xmdatzml ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid(+)
group by da.xtrhzrcs,
da.daid,
da.nd,
da.xmmc,
da.xtrhzrcsdm,
da.status
order by da.xtrhzrcs) xm,
--阶段
(select da.daid,
da.xmmc,
count(ml.jduanmc) jduansl --阶段数量
from yw_xmda da,
(select distinct l.jduanmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid(+)
group by da.daid, da.xmmc) jduan
where xm.daid = jduan.daid) a,
--节点
(select da.daid, da.xmmc, count(ml.jdianmc) jdiansl --阶点数量
from yw_xmda da,
(select distinct l.jdianmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017' --参数
--and da.xmmc like '%%'--参数
and da.daid = ml.daid(+)
group by da.daid, da.xmmc) jdian
where a.daid = jdian.daid) qt,
--文档
(select mda.daid, mda.xmmc, count(ad.filename) wdsl --文档数量
from yw_xmda mda, yw_xmdatzml zml, app_filesupload ad
where
1 = 1
and mda.nd = '2017' --参数
--and mda.xmmc like '%%'--参数
and zml.jdid = ad.operation_id(+)
and mda.daid = zml.daid(+)
group by mda.daid, mda.xmmc) wd
where qt.daid = wd.daid
UNION
--合计
select '' daid,
zs.xmmc,
zs.csmc,
zs.pid,
zs.cid,
zs.nd,
sum(zs.jduansl) jduansl, --阶段总数
sum(zs.jdiansl) jdiansl, --节点总数
sum(zs.wdsl) wdsl, --文档总数
sum(zs.xmsl) xmsl, --项目总数
'3' status
from (select qt.xmmc,
qt.csmc,
qt.pid,
qt.cid,
qt.nd,
qt.jduansl,
qt.jdiansl,
qt.xmsl,
wd.wdsl
from (select mda.xtrhzrcsdm, count(ad.filename) wdsl --文档数量
from app_filesupload ad, yw_xmdatzml zml, yw_xmda mda
where 1 = 1
and mda.nd = '2017'
-- and mda.xmmc like '%%'
and ad.operation_id = zml.jdid
and zml.daid = mda.daid
group by mda.xtrhzrcsdm) wd,
--项目数量
(select d.xtrhzrcsdm,
d.xmmc,
d.csmc,
d.pid,
d.cid,
d.nd,
d.jduansl,
d.jdiansl,
c.xmsl
from (select a.xtrhzrcsdm, count(a.xmmc) xmsl
from yw_xmda a
where 1 = 1
and a.nd = '2017'
-- and a.xmmc like '%%'
group by a.xtrhzrcsdm) c,
(select b.xtrhzrcsdm,
b.xmmc,
b.csmc,
b.pid,
b.cid,
b.nd,
b.jduansl,
a.jdiansl
from (select da.xtrhzrcsdm,
count(ml.jdianmc) jdiansl --阶点数量
from yw_xmda da,
(select distinct l.jdianmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017'
-- and da.xmmc like '%%'
and da.daid = ml.daid
group by da.xtrhzrcsdm) a,
(select '合计' xmmc,
da.xtrhzrcsdm,
'' csmc,
'0' pid,
'100' cid,
da.nd,
count(ml.jduanmc) jduansl --阶段数量
from yw_xmda da,
(select distinct l.jduanmc, l.daid
from yw_xmdatzml l) ml
where 1 = 1
and da.nd = '2017'
-- and da.xmmc like '%%'
and da.daid = ml.daid
group by da.nd, da.xtrhzrcsdm) b
where A.xtrhzrcsdm = B.xtrhzrcsdm) d
where c.xtrhzrcsdm = d.xtrhzrcsdm
group by d.xtrhzrcsdm,
d.xmmc,
d.csmc,
d.pid,
d.cid,
d.nd,
d.jduansl,
d.jdiansl,
c.xmsl) qt
where wd.xtrhzrcsdm(+) = qt.xtrhzrcsdm) zs
group by zs.xmmc, zs.csmc, zs.pid, zs.cid, zs.nd) tt
start with tt.PID = '0' /*and tt.nd = '2017'*/
connect by prior tt.CID = tt.PID
connect by prior tt.CID = tt.PID