在分层查询中查找“最深”的子节点

Question

我需要一些帮助来查询分层数据。下面是一个简单的表格，其中parent_id引用id，对于根目录的条目可能为null。

  create table edition (
      id           NUMBER(20),
      parent_id    NUMBER(20)
  );

对于表中的每个记录，我需要找到拥有最大id的最深子级。如果一条记录没有子级，则应返回其自己的id。我尝试过使用START WITH A.id = B.id进行自我操作，其中A和B是子查询，但似乎Oracle不允许这种连接方式。

以下是示例数据：

     id      parent_id
   ----------------------
      1        NULL
      2           1
      3           1
      4           1
      5           4
      6           5
      7           5

和一个示例结果

     id      result
   ----------------------
      1           7
      2           2
      3           3
      4           7
      5           7
      6           6
      7           7

- andbi

---

你能发布一些样本数据和期望的结果吗？ - Taryn

你可以看到我的方法。http://dba.stackexchange.com/questions/64350/how-can-i-select-rows-from-a-hierarchical-query-with-the-lowest-level/123299#123299 - QMaster

Answer 1

我相信你想尝试一下

create table tq84_edition (
  id        number primary key,
  parent_id number references tq84_edition
);

insert into tq84_edition values (  1, null);
insert into tq84_edition values (  2,    1);
insert into tq84_edition values (  3,    1);
insert into tq84_edition values (  4,    1);
insert into tq84_edition values (  5,    4);
insert into tq84_edition values (  6,    5);
insert into tq84_edition values (  7,    5);


with x (root, id, parent_id, lvl) as (
               select id    root,
                      id,
                      parent_id,
                      1 lvl
                from  tq84_edition
        UNION ALL
               select x.root  root,
                      tq84_edition.id,
                      tq84_edition.parent_id,
                      x.lvl + 1 lvl
                 from x,
                      tq84_edition
                where x.id = tq84_edition.parent_id
)
select  root, max(id) keep (dense_rank last order by lvl, id)
  from x
 group by root;

- René Nyffenegger

Answer 2

我能想到的另一种方法是，更易于在其他关系型数据库管理系统上进行移植：http://www.sqlfiddle.com/#!4/da0a3/19

with parent(root_node, child_of, parent_id, depth)  as
(
  select id, id, parent_id, 1
  from edition
  union all
  select p.root_node, e.id, e.parent_id, p.depth + 1
  from edition e
  join parent p on p.child_of = e.parent_id
)
select root_node, max(child_of)
from parent
where (root_node,depth) in
     (select root_node,max(depth) from parent group by root_node)
group by root_node
order by root_node

输出：

| ROOT_NODE | MAX(CHILD_OF) |
-----------------------------
|         1 |             7 |
|         2 |             2 |
|         3 |             3 |
|         4 |             7 |
|         5 |             7 |
|         6 |             6 |
|         7 |             7 |

现在，我非常喜欢Oracle（还有http://sqlfiddle.com），它非常简洁。现在我知道在KEEP DENSE_RANK中指定MIN和MAX的用途，而以前我没有看到任何实用性明确指定KEEP DENSE_RANK中的MIN/MAX。现在我知道它有一个用途，如果深度上存在一些ties，您可以使用MIN和MAX来查看谁是ties中的第一个和最后一个。例如http://www.sqlfiddle.com/#!4/da0a3/24。

with parent(root_node, child_of, parent_id, depth)  as
(
  select id, id, parent_id, 1
  from edition
  union all
  select p.root_node, e.id, e.parent_id, p.depth + 1
  from edition e
  join parent p on p.child_of = e.parent_id
)
select root_node, 

  min(child_of) keep(dense_rank last order by depth) as first_in_deepest,
  max(child_of) keep(dense_rank last order by depth) as last_in_deepest

from parent
group by root_node;






| ROOT_NODE | FIRST_IN_DEEPEST | LAST_IN_DEEPEST |
--------------------------------------------------
|         1 |                6 |               7 |
|         2 |                2 |               2 |
|         3 |                3 |               3 |
|         4 |                6 |               7 |
|         5 |                6 |               7 |
|         6 |                6 |               6 |
|         7 |                7 |               7 |

- Michael Buen

---

谢谢！你的回答让我感到更舒适了。现在我明白了在使用mysql时错过了多少东西。顺便说一下，你的查询可以用作快速刷新物化视图的源，对吗？ - andbi

嗯...我还没有深入研究Oracle中的物化视图，我无法评估我上面的查询是否适合在Oracle中进行视图物化。虽然我已经尝试过Sql Server中的物化视图。物化视图在Sql Server中被称为索引视图，但它们完全相同。Sql Server的物化视图是自动的，在那里没有快速（或缓慢）刷新的概念。 - Michael Buen