Oracle SQL 循环自连接

Question

背景:
假设我有一张表，其中有一个外键参照该表自己的主键，像这样:

|---------------------|------------------|------------------|
|          ID         |       NAME       |     PARENT_ID    |
|---------------------|------------------|------------------|
|          01         |       John       |         04       |
|---------------------|------------------|------------------|
|          02         |       Paul       |         01       |
|---------------------|------------------|------------------|
|          03         |       George     |         02       |
|---------------------|------------------|------------------|
|          04         |       Ringo      |         03       |
|---------------------|------------------|------------------|

问题：
正如你所看到的，存在循环的层次结构：Ringo->George->Paul->John->Ringo->George->Paul->John->等。

问题：
是否有一种SQL选择可以检测到这样的循环？

我知道我可以编写递归的PL/SQL过程，但我更喜欢使用“纯”SQL的解决方案。

提前谢谢您

- michal.d

---

一个带有循环检测的递归CTE/With？ - jarlh

这是一个常见问题解答。在考虑发布之前，请阅读手册并谷歌任何错误消息或您的问题/目标的许多清晰、简明和精确的措辞，包括和不包括您特定的字符串/名称和网站：stackoverflow.com和标签；阅读许多答案。如果您发布一个问题，请使用一个措辞作为标题。反映您的研究。请参见[ask]和投票箭头悬停文本。 - philipxy

Answer 1

您可以使用 CONNECT BY 查询与 CONNECT_BY_ISCYCLE 伪列查找循环 - 参见来自Oracle文档的示例：

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle"
FROM employees
WHERE level <= 3 
AND   department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

- Tony Andrews

Answer 2

您可以使用 connect by nocycle 和 connect_by_iscycle 来实现这一点。对于您的表结构，它应该是这样的:

select id, name, parent_id, connect_by_iscycle
from mytable
connect by nocycle id = prior parent_id
start with id = 4

connect by nocycle会在遇到循环时停止迭代查询，伪列connect_by_iscycle包含一个指示它发生的位置的标志，如在此演示中所示：

ID | NAME   | PARENT_ID | CONNECT_BY_ISCYCLE
-: | :----- | --------: | -----------------:
 4 | Ringo  |         3 |                  0
 3 | George |         2 |                  0
 2 | Paul   |         1 |                  0
 1 | John   |         4 |                  1  --> 在这里检测到了循环

- GMB

Answer 3

这里有一个具有递归 CTE 的解决方案：

with cte (id, parent_id, ids) as
(
  select id, parent_id, to_char(id) from mytable
  union all
  select t.id, t.parent_id, ids || ' -> ' || t.id
  from cte
  join mytable t on t.id = cte.parent_id
)
cylce id set cycle to 1 default 0
select ids as cycling_ids
from cte
where cycle = 1
order by ids;

结果：

+ ----------------------+
| CYCLING_IDS           |
+ ----------------------+
| 1 -> 4 -> 3 -> 2 -> 1 |
| 2 -> 1 -> 4 -> 3 -> 2 |
| 3 -> 2 -> 1 -> 4 -> 3 |
| 4 -> 3 -> 2 -> 1 -> 4 |
+ ----------------------+

如果您只想看到每个循环一次（我假设是这样），请记住每个循环的最小ID，并仅显示一个最小ID的循环：

with cte (id, parent_id, ids, min_id) as
(
  select id, parent_id, to_char(id), id from mytable
  union all
  select t.id, t.parent_id, ids || ' -> ' || t.id, least(t.id, cte.min_id)
  from cte
  join mytable t on t.id = cte.parent_id
)
cycle id set cycle to 1 default 0
select min(ids) as cycling_ids
from cte
where cycle = 1
group by min_id
order by min_id;

结果：

+ ----------------------+
| CYCLING_IDS           |
+ ----------------------+
| 1 -> 4 -> 3 -> 2 -> 1 |
+ ----------------------+

演示更多ID和不同情况：https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f7f924cd8759d67a188b7c11f2d071ef

（这仍然不完美。如果一个非常小的ID导致更高的ID形成循环，例如如果我们插入一个ID 0也引用ID 3作为父级，则查询将显示循环超过一次。这很难避免，因为我们必须检测圆圈内的最小ID。我可能会编写一个小的PL/SQL函数来从IDs字符串中获取此最小ID。）

- Thorsten Kettner