Pl /sql分层数据排列

Question

Table tMain:

<>之前+----+-------+---------+| ID |名称| id_ref |+----+-------+---------+[1]胺[4]| . .| | |+----+-------+---------+之前

表tTree:

<>之前+----+--------+-----------+| ID |名称| id_parent |+----+--------+-----------+| 1 | root | null || 2 | child1 | 1 || 3 |孩子2 | 2 || 4 |孩子3 | 3 |+----+--------+-----------+之前

所以我有我想"连接"的表。表"tMain"包含一些数据和一个引用tree表的外键(引用最低的子项)。我想要的是使用一个查询从tMain获得所有数据，以及从tretree组装的路径。它看起来像这样:

<>之前+----+-------+--------+---------------------------+| ID |名称| id_ref |路径|+----+-------+--------+---------------------------+| 1 | amine | 4 | root/child1/child2/child3 || . .| | | |+----+-------+--------+---------------------------+

Answer 1

WITH tree$ AS (
    SELECT T.id, ltrim(sys_connect_by_path(name, '/'), '/') AS path
    FROM tTree T
    START WITH id_parent IS NULL
    CONNECT BY PRIOR ID = id_parent
)
SELECT T.id, M.name, M.id_ref, T.path
FROM tMain M
    JOIN tree$ T ON T.id = M.id_ref
;