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

2021-12-05 00:00:00 sql database hierarchical-data oracle plsql

假设我有这个样本数据:

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         |

我想选择从 accountId = 1 开始的所有记录，所以预期的结果是:

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

| 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.我使用的代码是(适用于简单的例子):

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

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

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

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

未来搜索者的额外关键词:SQL 选择由同一个表中的父键表示的分层数据

推荐答案

使用:

    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

相关文章