有没有一种方法可以在SQL Server的分层查询中检测周期? [英] Is there a way to detect a cycle in Hierarchical Queries in SQL Server?

问题描述

在Oracle中，我们可以使用函数 CONNECT_BY_ISCYCLE 来检测层次查询"中的循环.我尝试在SQL Server中执行相同的操作.有办法吗?

In Oracle, we can use the function CONNECT_BY_ISCYCLE to detect a cycle in Hierarchical Queries. I try to do the same in SQL Server. Is there a way to do this?

非常感谢！

推荐答案

连接记录ID/基于记录的ROW_NUMBER个构建位图，并对照列表/位图验证每个新记录

Concatenate the records IDs / build a bitmap based on ROW_NUMBERs of the records and verify each new record against the list/bitmap

create table t (id int,pid int)
insert into t values (1,3),(2,1),(3,2)

---

列表

确定周期

with cte (id,pid,list,is_cycle) 
as
(
    select      id,pid,',' + cast (id as varchar(max))  + ',',0
    from        t
    where       id = 1

    union all

    select      t.id,t.pid,cte.list + cast (t.id as varchar(10)) +  ',' ,case when cte.list like '%,' + cast (t.id as varchar(10)) + ',%' then 1 else 0 end
    from        cte join t on t.pid = cte.id
    where       cte.is_cycle = 0
)
select      *
from        cte
where       is_cycle = 1

---

id  pid list        is_cycle
--  --- ----        --------
1   3   ,1,2,3,1,   1

---

遍历遍历遍历图

with cte (id,pid,list) 
as
(
    select      id,pid,',' + cast (id as varchar(max))  + ','
    from        t
    where       id = 1

    union all

    select      t.id,t.pid,cte.list + cast (t.id as varchar(10)) +  ',' 
    from        cte join t on t.pid = cte.id
    where       cte.list not like '%,' + cast (t.id as varchar(10)) + ',%'
)
select      *
from        cte

---

id  pid list
1   3   ,1,
2   1   ,1,2,
3   2   ,1,2,3,

位图

ID应该是一个以1开头的数字序列.
如有必要，请使用ROW_NUMBER生成它.

Bitmap

ID should be a sequence of numbers starting with 1.
If necessary generate it using ROW_NUMBER.

with cte (id,pid,bitmap,is_cycle) 
as
(
    select      id,pid,cast (power(2,id-1) as varbinary(max)) ,0
    from        t
    where       id = 1

    union all

    select      t.id,t.pid,cast (cte.bitmap|power(2,t.id-1) as varbinary(max)),case when cte.bitmap & power(2,t.id-1) > 0 then 1 else 0 end
    from        cte join t on t.pid = cte.id
    where       cte.is_cycle = 0
)
select      *
from        cte
where       is_cycle = 1

---

id  pid bitmap      is_cycle
1   3   0x00000007  1

遍历遍历遍历图

with cte (id,pid,bitmap) 
as
(
    select      id,pid,cast (power(2,id-1) as varbinary(max))
    from        t
    where       id = 1

    union all

    select      t.id,t.pid,cast (cte.bitmap|power(2,t.id-1) as varbinary(max))
    from        cte join t on t.pid = cte.id
    where       cte.bitmap & power(2,t.id-1) = 0
)
select      *
from        cte

---

id  pid bitmap
1   3   0x00000001
2   1   0x00000003
3   2   0x00000007

这篇关于有没有一种方法可以在SQL Server的分层查询中检测周期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！