如何从Oracle中的链表中获取第一个元素?

I have a table in my Oracle-DB that looks like this:

我的Oracle-DB中有一张表是这样的:

╔════╦════════════════╦═════════════╗
║ ID ║ Predecessor_id ║ Information ║
╠════╬════════════════╬═════════════╣
║ 1  ║     NULL       ║    foo      ║
║ 2  ║     1          ║    bar      ║
║ 3  ║     2          ║    muh      ║
║ 4  ║     NULL       ║    what     ║
║ 5  ║     4          ║    ever     ║
╚════╩════════════════╩═════════════╝

I need a SELECT that returns something like this:

我需要一个选择，返回如下内容:

╔════╦════════════════╦════════════════════╦═════════════╗
║ ID ║ Predecessor_id ║ First_list_element ║ Information ║
╠════╬════════════════╬════════════════════╬═════════════╣
║ 1  ║     NULL       ║         1          ║    foo      ║
║ 2  ║     1          ║         1          ║    bar      ║
║ 3  ║     2          ║         1          ║    muh      ║
║ 4  ║     NULL       ║         4          ║    what     ║
║ 5  ║     4          ║         4          ║    ever     ║
╚════╩════════════════╩════════════════════╩═════════════╝

The table has some kind of linked list aspect. The first element of the List is the one with no predecessor. What I need is the information for every row in which List it is a member of. The list is defined by the ID of the first element.

该表具有某种链表方面。列表的第一个元素是没有前任的元素。我需要的是列表中的每一行的信息。列表由第一个元素的ID定义。

In a programming language I would implement some kind of lookup table. But in SQL I have no idea. I would prefer SQL but if only PL/SQL gives me a response I'll take that as well.

在编程语言中，我将实现某种查找表。但是在SQL中我不知道。我更喜欢SQL，但如果PL/SQL给我一个响应，我也会接受它。

1 个解决方案

#1

You should use CONNECT_BY_ROOT operator

您应该使用CONNECT_BY_ROOT操作符。

When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators004.htm#i1035022

当使用该操作符限定列时，Oracle使用根行中的数据返回列值。这个操作符扩展了连接的功能(之前)的层次查询条件。http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators004.htm i1035022

http://sqlfiddle.com/#!4/121f02/1

http://sqlfiddle.com/ ! 4/121f02/1

create table  tbl(
id number,
Predecessor_id number,
Information varchar2(250));


insert into tbl values(1 ,NULL          ,'foo'    );
insert into tbl values(2 ,1             ,'bar'    );
insert into tbl values(3 ,2             ,'muh'    );
insert into tbl values(4 ,NULL          ,'what'   );
insert into tbl values(5 ,4             ,'ever'   );

SELECT tbl.*, connect_by_root id first_list_element
  FROM tbl
CONNECT BY PRIOR id = predecessor_id
 START WITH predecessor_id IS NULL

#1