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