自引用表上的SQL递归查询(Oracle) [英] SQL recursive query on self referencing table (Oracle)
本文介绍了自引用表上的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 |
我想选择所有从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
这篇关于自引用表上的SQL递归查询(Oracle)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文