将邻接列表转换为枚举路径ORACLE

Question

如何在Oracle中创建一个将邻接列表转换为枚举路径的过程(通过创建一个新表作为枚举路径(？邻接列表是这样一个表：

AdjList(id number, parent_id number, name varchar2)
ID PARENT_ID  NAME
-----------------------------
1             CATEGORY1
2    1        PROD1
3             CATEGORY2
4    3        PROD2
5    4        SUBPROD1

期望输出：

EnumPath(id number, parent_id number, name varchar2, path varchar2)
ID PARENT_ID  NAME          PATH
-----------------------------------
1             CATEGORY1     /1/
2    1        PROD1         /1/2/
3             CATEGORY2     /3/
4    3        PROD2         /3/4/
5    4        SUBPROD1      /3/4/5/

Answer 1

在我看来像是sys_connect_by_path层次查询。

SQL> with test (id, parent_id, name) as
2    (select 1, null, 'category1' from dual union all
3     select 2, 1   , 'prod1' from dual union all
4     select 3, null, 'category2' from dual union all
5     select 4, 3   , 'prod2' from dual union all
6     select 5, 4   , 'subprod1' from dual
7    )
8  select id, parent_id, name,
9    ltrim(sys_connect_by_path(id, '>'), '>') path
10  from test
11  connect by prior id = parent_id
12  start with parent_id is null;
ID  PARENT_ID NAME      PATH
---------- ---------- --------- --------------------
1            category1 1
2          1 prod1     1>2
3            category2 3
4          3 prod2     3>4
5          4 subprod1  3>4>5
SQL>

Answer 2

没有数据很难说，但如果你有类似的数据

ID    PARENT_ID    NAME
1     NULL         TOP
2     1            L2
3     2            L3
4     3            BOTTOM

你正在寻找类似的东西

TOP -> L2 -> L3 -> BOTTOM

您可以使用诸如之类的查询

SELECT LISTAGG(NAME, ' -> ') WITHIN GROUP(ORDER BY LEVEL) AS PATH
FROM ADJLIST
CONNECT BY PARENT_ID = PRIOR ID
START WITH ID = (SELECT ID FROM ADJLIST WHERE PARENT_ID IS NULL)

db<gt；在这里摆弄