自引用表的SQL递归查询(Oracle)

Lets assume I have this sample data:

假设我有这个样本数据:

| Name     | ID | PARENT_ID |
-----------------------------
| a1       | 1  | null      |
| b2       | 2  | null      |
| c3       | 3  | null      |
| a1.d4    | 4  | 1         |
| a1.e5    | 5  | 1         |
| a1.d4.f6 | 6  | 4         |
| a1.d4.g7 | 7  | 4         |
| a1.e5.h8 | 8  | 5         |
| a2.i9    | 9  | 2         |
| a2.i9.j10| 10 | 9         |

I would like to select all records start from accountId = 1, so the expected result would be:

我想从accountId = 1开始选择所有记录，预计结果为:

| Name     | ID | PARENT_NAME | PARENT_ID | 
-------------------------------------------
| a1       | 1  | null        | null      |
| a1.d4    | 4  | a1          | 1         |
| a1.e5    | 5  | a1          | 1         |
| a1.d4.f6 | 6  | a1.d4       | 4         |
| a1.d4.g7 | 7  | a1.d4       | 4         |
| a1.e5.h8 | 8  | a1.e5       | 5         |

I am currently able to make the recursive select, but then I can't access the data from the parent reference, hence I can't return parent_name. The code I'm using is (adapted to the simplistic example):

目前我可以进行递归选择，但是我不能从父引用访问数据，因此不能返回parent_name。我使用的代码是(适用于这个简单的例子):

SELECT id, parent_id, name
FROM tbl 
  START WITH id = 1 
  CONNECT BY PRIOR id = parent_id

What SQL should I be using to the mentioned above retrieval?

对于上述检索，我应该使用什么SQL ?

Additional key words for future seekers: SQL to select hierarchical data represented by parent keys in same table

为将来的搜索者提供的其他关键字:SQL，用于在同一表中选择由父键表示的层次数据

5 个解决方案

#1

Use:

使用:

    SELECT t1.id, 
           t1.parent_id, 
           t1.name,
           t2.name AS parent_name,
           t2.id AS parent_id
      FROM tbl t1
 LEFT JOIN tbl t2 ON t2.id = t1.parent_id
START WITH t1.id = 1 
CONNECT BY PRIOR t1.id = t1.parent_id

#2

What about using PRIOR,

使用前,

所以

SELECT id, parent_id, PRIOR name
   FROM tbl 
START WITH id = 1 
CONNECT BY PRIOR id = parent_id`

or if you want to get the root name

或者你想知道根名

SELECT id, parent_id, CONNECT_BY_ROOT name
   FROM tbl 
START WITH id = 1 
CONNECT BY PRIOR id = parent_id

#3

Using the new nested query syntax

使用新的嵌套查询语法

with q(name, id, parent_id, parent_name) as (
    select 
      t1.name, t1.id, 
      null as parent_id, null as parent_name 
    from t1
    where t1.id = 1
  union all
    select 
      t1.name, t1.id, 
      q.id as parent_id, q.name as parent_name 
    from t1, q
    where t1.parent_id = q.id
)
select * from q

#4

Do you want to do this?

你想这样做吗?

SELECT id, parent_id, name, 
 (select Name from tbl where id = t.parent_id) parent_name
FROM tbl t start with id = 1 CONNECT BY PRIOR id = parent_id

Edit Another option based on OMG's one (but I think that will perform equally):

根据OMG的一个选项编辑另一个选项(但我认为这将是同样的):

select 
           t1.id, 
           t1.parent_id, 
           t1.name,
           t2.name AS parent_name,
           t2.id AS parent_id
from 
    (select id, parent_id, name
    from tbl
    start with id = 1 
    connect by prior id = parent_id) t1
    left join
    tbl t2 on t2.id = t1.parent_id

#5

It's a little on the cumbersome side, but I believe this should work (without the extra join). This assumes that you can choose a character that will never appear in the field in question, to act as a separator.

这有点麻烦，但我相信这应该是可行的(没有额外的连接)。这假定您可以选择一个永远不会出现在该字段中的字符作为分隔符。

You can do it without nesting the select, but I find this a little cleaner that having four references to SYS_CONNECT_BY_PATH.

您可以在不嵌套select的情况下完成它，但是我发现这个更简洁，它有四个对SYS_CONNECT_BY_PATH的引用。

select id, 
       parent_id, 
       case 
         when lvl <> 1 
         then substr(name_path,
                     instr(name_path,'|',1,lvl-1)+1,
                     instr(name_path,'|',1,lvl)
                      -instr(name_path,'|',1,lvl-1)-1) 
         end as name 
from (
  SELECT id, parent_id, sys_connect_by_path(name,'|') as name_path, level as lvl
  FROM tbl 
  START WITH id = 1 
  CONNECT BY PRIOR id = parent_id)

#1