Oracle中关于树递归的一个sql分析

总的：select distinct t.id tenantid,

                t.name,
                ts.scnames,
                ts.scids,
                t.remark
  from PM_TENANT t
 inner join (select tenantid,
                    max(substr(scnames, 2)) scnames,--max取scname中包含的个数最多的；substr去第二个字符后面的字符
                    max(substr(scids, 2)) scids
               from (select tenantid,
                            sys_connect_by_path(scnames, ',') scnames,
                            sys_connect_by_path(scids, ',') scids
                       from (select tenantid,
                                    scnames,
                                    scids,
                                    tenantid || rn rchild,--字符串连接
                                    tenantid || (rn - 1) rfather,rn
                               from (select ts.tenantid,
                                            s.name scnames,
                                            s.id scids,
                                            row_number() over(partition by ts.tenantid order by ts.scid) rn  --行序号：以tenenti为分界，不同则从0开始
                                       from pm_tenant_sc ts
                                       left join pm_servicecomponent s on ts.scid = s.id))
                     start with rfather like '%0'--以父节点最后的字符为0的开始
                     connect by prior rchild = rfather--prior 从父节点向下遍历子节点
                     )
              group by tenantid) ts on t.id = ts.tenantid

 

下面来分析：

1：选出行号，不同tenentid，rn从头算，相同tenantid，rn则递增

 

select ts.tenantid,
             s.name scnames,
             s.id scids,
             row_number() over(partition by ts.tenantid order by ts.scid) rn  --行序号：以tenenti为分界，不同则从0开始
             from pm_tenant_sc ts
             left join pm_servicecomponent s on ts.scid = s.id

 

2：得到右孩子与父亲，如10200--》10201--》10202：10200

---》coach（10201）

---》Billy（10202）

 

select tenantid,
                                    scnames,
                                    scids,
                                    tenantid || rn rchild,--字符串连接（tenantid+rn-->rchild）
                                    tenantid || (rn - 1) rfather,rn--tenantid+rn-1-->rfather
                               from (select ts.tenantid,
                                            s.name scnames,
                                            s.id scids,
                                            row_number() over(partition by ts.tenantid order by ts.scid) rn  --行序号：以tenenti为分界，不同则从0开始
                                       from pm_tenant_sc ts
                                       left join pm_servicecomponent s on ts.scid = s.id)

 

3：形成树，用逗号连接各个scnames,scids

 

select tenantid,
                            sys_connect_by_path(scnames, ',') scnames,
                            sys_connect_by_path(scids, ',') scids
                       from (select tenantid,
                                    scnames,
                                    scids,
                                    tenantid || rn rchild,--字符串连接
                                    tenantid || (rn - 1) rfather,rn
                               from (select ts.tenantid,
                                            s.name scnames,
                                            s.id scids,
                                            row_number() over(partition by ts.tenantid order by ts.scid) rn  --行序号：以tenenti为分界，不同则从0开始
                                       from pm_tenant_sc ts
                                       left join pm_servicecomponent s on ts.scid = s.id))
                     start with rfather like '%0'--以父节点最后的字符为0的开始
                     connect by prior rchild = rfather--prior 从父节点向下遍历子节点

4：去掉前面的逗号，并且取scnames,scids最多的那项（去重复）

 

select tenantid,
                    max(substr(scnames, 2)) scnames,--max取scname中包含的个数最多的；substr去第二个字符后面的字符
                    max(substr(scids, 2)) scids
               from (select tenantid,
                            sys_connect_by_path(scnames, ',') scnames,
                            sys_connect_by_path(scids, ',') scids
                       from (select tenantid,
                                    scnames,
                                    scids,
                                    tenantid || rn rchild,--字符串连接
                                    tenantid || (rn - 1) rfather,rn
                               from (select ts.tenantid,
                                            s.name scnames,
                                            s.id scids,
                                            row_number() over(partition by ts.tenantid order by ts.scid) rn  --行序号：以tenenti为分界，不同则从0开始
                                       from pm_tenant_sc ts
                                       left join pm_servicecomponent s on ts.scid = s.id))
                     start with rfather like '%0'--以父节点最后的字符为0的开始
                     connect by prior rchild = rfather--prior 从父节点向下遍历子节点
                     )
              group by tenantid

 

   

 

 

评论

1 楼 laoli5290 2012-06-25  

分享一下，呵呵！