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

Question

假设我有这个示例数据：

| 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         |

我想选择从 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         |

我当前能够进行递归选择，但随后我无法访问父级的数据引用，因此我无法返回parent_name。我使用的代码是（适用于简单的示例）：

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

我应该使用什么 SQL 来进行上述检索？

供未来搜索者使用的其他关键字：用于选择由同一表中的父键表示的分层数据的 SQL

Answer 1

使用：

    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

Answer 2

使用 PRIOR 怎么样，

所以

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

或者如果你想获取根名称

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

Answer 3

使用新的嵌套查询语法

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

Answer 4

你想这样做吗？

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

编辑
另一种基于 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

Answer 5

这有点麻烦，但我相信这应该可行（无需额外的连接）。这假设您可以选择一个永远不会出现在相关字段中的字符来充当分隔符。

您可以在不嵌套选择的情况下完成此操作，但我发现这更干净一点，有四个对 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)