Oracle中关于树递归的一个sql分析-优快云博客

本文介绍了一个复杂的SQL查询案例，该查询用于构建一个基于租户ID的层级结构，并从中选取特定的数据进行汇总。通过多表连接、窗口函数及递归查询等高级SQL技巧，实现了对多层级组织结构的有效提取。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

总的：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