sql组装一棵树

先说一下什么是递归查询，简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: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

 start with tt.PID = '0' /*and  tt.nd = '2017'*/
connect by prior tt.CID = tt.PID