sql - 分层查询中的 Oracle 祖先

Question

给定包含数据的表T_Person(姓名，父级)

+--------+--------+| name   | parent |+--------+--------+| john   | peter  || peter  | ronald || ronald | george || george |        |+--------+--------+

one can find relations using this query:

select name, parent, LEVEL
from T_Person
connect by prior name = parent
start with parent is null;

结果将是:

+--------+--------+-------+| name   | parent | LEVEL |+--------+--------+-------+| john   | peter  | 4     || peter  | ronald | 3     || ronald | george | 2     || george |        | 1     |+--------+--------+-------+

到目前为止还好。但我想要一个包含所有看起来像这样的关系的结果:

+--------+--------+----------------+| name   | parent | relation_level |+--------+--------+----------------+| john   | peter  | 1              || peter  | ronald | 1              || ronald | george | 1              || john   | ronald | 2              || peter  | george | 2              || john   | george | 3              |+--------+--------+----------------+

(关系级别:1 = 父亲，2 = 祖父，3 = 曾祖父，依此类推)

除了为每个关系级别选择整个表之外，是否有一种快速的 Oracle 方法来接收此结果？

Answer 1

或者您可以删除start with 子句

SELECT name, ancestor AS parent, l
FROM
(
  SELECT name, parent, LEVEL-1 l, connect_by_root name ancestor
    FROM T_Person
 CONNECT BY PRIOR name = parent
) t
WHERE t.ancestor <> t.name

Here is a sqlfiddle demo