时间戳

首页 > 解决方案 > oracle：查询以输出分隔 ID 作为参考代码

sql - oracle：查询以输出分隔 ID 作为参考代码

问题描述

有一个具有业务单位树结构的表。

BUT_ID  PARENT_ID  REFERENCE_CODE  BUT_PATH
173     10         SBT022          /1/2/10/173
174     8          SEM05000        /1/2/8/174
175     10         SBT023          /1/2/10/175
2       1          O               /1/2
8       2          SP              /1/2/8
1       null       root            null

BUT_PATH业务树中到当前业务单元的路径在哪里。

我在编写查询以输出BUT_PATH具有相应参考代码而不是 id 的结构时遇到了困难。例如，/1/2/8/174它就像/O/SP/SEM05000在 Oracle 12c 中一样。

标签： sqloracle

解决方案

我们可以通过首先创建与路径中的节点一样多的行的副本来实现这一点。这可以通过使用ROWNUM伪列将表与自身连接来完成 - 连接ROWNUM <=路径中的节点数，我们可以通过使用REGEXP_COUNT()来计算/.

对于具有n 个节点的每条路径，我们现在有n行，其数字RN从 1 到n。REGEXP_REPLACE()我们可以从路径中提取第 th 节点的ID RN。

我们再次加入表，这次使用RN路径中的那个 ID。像这样，我们RN在相应行中有第 th 节点的引用。

最后我们聚合并使用LISTAGG()将引用的路径放在一起。

SELECT T1.BUT_ID,
       T1.PARENT_ID,
       T1.REFERENCE_CODE,
       T1.BUT_PATH,
       '/' || LISTAGG(T3.REFERENCE_CODE, '/') WITHIN GROUP (ORDER BY X1.RN) REFERENCE_PATH
       FROM ELBAT T1
            INNER JOIN (SELECT ROWNUM RN
                               FROM ELBAT T2) X1
                       ON X1.RN <= REGEXP_COUNT(T1.BUT_PATH, '/')
            INNER JOIN ELBAT T3
                       ON T3.BUT_ID = TO_NUMBER(REGEXP_REPLACE(REGEXP_SUBSTR(T1.BUT_PATH, '/[^/]+', 1, X1.RN), '^/'))
       GROUP BY T1.BUT_ID,
                T1.PARENT_ID,
                T1.REFERENCE_CODE,
                T1.BUT_PATH;

db<>小提琴

推荐阅读