gpt4 book ai didi

sql - 寻找CTE中的无限递归循环

转载 作者:行者123 更新时间:2023-12-01 19:38:24 26 4
gpt4 key购买 nike

我不是 SQL 专家,但如果有人可以帮助我。

我使用递归 CTE 来获取如下值。

子1 --> 父1

父级 1 --> 父级 2

父级2 --> NULL

如果数据填充出错,那么我会得到如下所示的结果,因为 CTE 可能会进入无限递归循环并给出最大递归错误。由于数据很大,我无法手动检查这个坏数据。请告诉我是否有办法找到它。

子1 --> 父1

父级1 --> 子级1

子1 --> 父1

父级1 --> 父级2

父级2 --> 子级1

最佳答案

使用 Postgres,通过将所有访问过的节点收集到一个数组中,可以很容易地防止这种情况发生。

设置:

create table hierarchy (id integer, parent_id integer);

insert into hierarchy
values
(1, null), -- root element
(2, 1), -- first child
(3, 1), -- second child
(4, 3),
(5, 4),
(3, 5); -- endless loop

递归查询:

with recursive tree as (
select id,
parent_id,
array[id] as all_parents
from hierarchy
where parent_id is null

union all

select c.id,
c.parent_id,
p.all_parents||c.id
from hierarchy c
join tree p
on c.parent_id = p.id
and c.id <> ALL (p.all_parents) -- this is the trick to exclude the endless loops
)
select *
from tree;
<小时/>

要同时对多棵树执行此操作,您需要将根节点的 ID 传递给子节点:

with recursive tree as (
select id,
parent_id,
array[id] as all_parents,
id as root_id
from hierarchy
where parent_id is null

union all

select c.id,
c.parent_id,
p.all_parents||c.id,
p.root_id
from hierarchy c
join tree p
on c.parent_id = p.id
and c.id <> ALL (p.all_parents) -- this is the trick to exclude the endless loops
and c.root_id = p.root_id
)
select *
from tree;

Postgres 14 更新

Postgres 14 引入了(符合标准)CYCLE 选项来检测周期:

with recursive tree as (
select id,
parent_id
from hierarchy
where parent_id is null

union all

select c.id,
c.parent_id
from hierarchy c
join tree p
on c.parent_id = p.id
)
cycle id -- track cycles for this column
set is_cycle -- adds a boolean column is_cycle
using path -- adds a column that contains all parents for the id
select *
from tree
where not is_cycle

关于sql - 寻找CTE中的无限递归循环,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31739150/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com