sql - 分层查询显示每个父级下的所有子级

Question

请看附件图片：

树的视觉图

所需的查询输出

例如，这是图像（图表）中所示的数据结构。我需要一个查询，它将在第二张图像中显示相同的结果（以显示每个父级下的所有子级）。

下面是我尝试过的代码（结果与我要找的不一样）：

 SELECT parent,
        LEVEL,
        child

FROM  table1 

CONNECT BY NOCYCLE parent = PRIOR child;

标签： sqloraclehierarchical-data

Answer 1

我将表中的列名从“CHILD”和“PARENT”分别更改为“ID”和“PARENT_ID”，以减少混淆。

无论如何，您需要使用CONNECT_BY_ROOT(parent_id)来获取在您想要的输出中显示为“PARENT”的内容。如果您不关心结果中行的顺序，这很简单：

SELECT connect_by_root(parent_id) "PARENT", id "CHILD"
FROM   table1
WHERE  connect_by_root(parent_id) is not null
CONNECT BY parent_id = prior id

如果您确实关心行的顺序，那就更难了。您需要检查树中每个节点的深度并使用它来对结果进行排序。那将是：

with hier as ( 
SELECT connect_by_root(parent_id) root_id, id, level, 
       case when connect_by_root(parent_id) is null then level else null end root_depth
FROM   table1
CONNECT BY parent_id = prior id
)
select h1.root_id "PARENT", h1.id "CHILD"
from hier h1 inner join hier h2 on h2.root_id is null and h2.id = h1.root_id
order by h2.root_depth, h1.root_id, h1.id

这是一个完整的示例，带有测试数据：

WITH table1 ( id, parent_id ) AS
( SELECT 'A', null FROM DUAL UNION ALL
  SELECT 'A1', 'A' FROM DUAL UNION ALL
  SELECT 'A2', 'A' FROM DUAL UNION ALL
  SELECT 'A3', 'A' FROM DUAL UNION ALL
  SELECT 'A11', 'A1' FROM DUAL UNION ALL
  SELECT 'A12', 'A1' FROM DUAL UNION ALL
  SELECT 'A21', 'A2' FROM DUAL UNION ALL
  SELECT 'A121', 'A12' FROM DUAL UNION ALL
  SELECT 'A122', 'A12' FROM DUAL ),
-- Solution begins here 
hier as ( 
SELECT connect_by_root(parent_id) root_id, id, level lvl, 
       case when connect_by_root(parent_id) is null then level else null end root_depth
FROM   table1
CONNECT BY parent_id = prior id
)
select h1.root_id "PARENT", h1.id "CHILD", h2.*
from hier h1 inner join hier h2 on h2.root_id is null and h2.id = h1.root_id
order by h2.root_depth, h1.root_id, h1.lvl, h1.id

+--------+-------+
| PARENT | CHILD |
+--------+-------+
| A      | A1    |
| A      | A2    |
| A      | A3    |
| A      | A11   |
| A      | A12   |
| A      | A21   |
| A      | A121  |
| A      | A122  |
| A1     | A11   |
| A1     | A12   |
| A1     | A121  |
| A1     | A122  |
| A2     | A21   |
| A12    | A121  |
| A12    | A122  |
+--------+-------+