分层查询如何显示每个父项下的所有子项

Question

我正在使用oracle sql，遇到了一个问题。

我有这个表：

<1><1><1>

ID	名称	parent_ID
1	A	NULL
2	A1
3	A2
4	A3
5	A11	2
6	A12	2
7	A21	3
8	A121	6
9	A122	6

您的查询看起来正常-部分正常；当被重用时，它会返回所需的结果。阅读代码中的注释。

样本数据：

SQL> with test (id, name, parent_id) as
2    (select 1, 'A'   ,  null from dual union all
3     select 2, 'A1'  , 1     from dual union all
4     select 3, 'A2'  , 1     from dual union all
5     select 4, 'A3'  , 1     from dual union all
6     select 5, 'A11' , 2     from dual union all
7     select 6, 'A12' , 2     from dual union all
8     select 7, 'A21' , 3     from dual union all
9     select 8, 'A121', 6     from dual union all
10     select 9, 'A122', 6     from dual
11    ),

查询从这里开始：

12  temp as
13    -- your query, with additional columns (child_name, lvl)
14    (select connect_by_root (a.parent_id) parent,
15            a.id child,
16            --
17            a.name child_name,
18            level lvl
19     from test a
20     where connect_by_root(a.parent_id) is not null
21     connect by a.parent_id = prior a.id
22    )
23  -- finally, join result of your query to sample data to extract parent name
24  select a.name parent_name,
25         t.child_name
26  from test a join temp t on a.id = t.parent
27  order by parent_name, lvl, child_name;

结果：

PARENT_NAME CHILD_NAME
----------- -----------
A           A1
A           A2
A           A3
A           A11
A           A12
A           A21
A           A121
A           A122
A1          A11
A1          A12
A1          A121
A1          A122
A12         A121
A12         A122
A2          A21
15 rows selected.
SQL>

您可以使用：

SELECT  CONNECT_BY_ROOT name AS ancestor,
name AS child
FROM    table1
WHERE   LEVEL > 1
CONNECT BY
parent_id = prior id

对于样本数据：

CREATE TABLE table1 (ID, name, parent_id) AS
SELECT 1, 'A',    NULL FROM DUAL UNION ALL
SELECT 2, 'A1',   1 FROM DUAL UNION ALL
SELECT 3, 'A2',   1 FROM DUAL UNION ALL
SELECT 4, 'A3',   1 FROM DUAL UNION ALL
SELECT 5, 'A11',  2 FROM DUAL UNION ALL
SELECT 6, 'A12',  2 FROM DUAL UNION ALL
SELECT 7, 'A21',  3 FROM DUAL UNION ALL
SELECT 8, 'A121', 6 FROM DUAL UNION ALL
SELECT 9, 'A122', 6 FROM DUAL;

输出：

ANCESTOR
A1	A11
A1
A1	A121
A1	A122
A2A12A12
A	A1
A	A11
A	A12
A	A121
A	A122
A	A3
A	A2
A	A21