使用递归查询仅查找自引用值

Question

>表：

create table tbl_nodess
(
nod1 varchar(50),
nod2 varchar(50)
);

记录：

insert into tbl_nodess values('A','B'); 
insert into tbl_nodess values('B','C');
insert into tbl_nodess values('C','B');
insert into tbl_nodess values('D','E');
insert into tbl_nodess values('E','F');
insert into tbl_nodess values('G','H');
insert into tbl_nodess values('B','D');
insert into tbl_nodess values('D','A');
insert into tbl_nodess values('I','J');
insert into tbl_nodess values('J','K');
insert into tbl_nodess values('K','L');
insert into tbl_nodess values('L','J');

预期成果：

self_ref_nodes 
------------------
A-B-C-B 
A-B-D-A
I-J-K-L-J

关于预期结果的解释：我只想找到那些自引用的节点。

例：

1. 节点A引用B，B指C，C指B

节点C再次引用节点B。

2. 节点A指B，B指D，D指A

节点再次D引用节点A。

3. nodeI指J，J指K，K指L，L指J

节点L再次引用节点J(已在链中)。

查询：

;with cte AS (
select nod1, nod2, 
convert(varchar(max), ('-'+ nod1+ '-'+ nod2+ '-')) as nodes, 1 as lev
from tbl_nodess n
where not exists(select 1 from tbl_nodess n1 where n.nod1 = n1.nod2)
union all
select cte.nod1, n.nod2, 
convert(varchar(max), (cte.nodes+ n.nod2+ '-')) as nodes, lev + 1
from cte join
tbl_nodess n
on cte.nod2 = n.nod1
where nodes not like ('%-'+ n.nod2+ '-%') 
)
select nodes
from cte
where not exists 
(select 1
from cte cte2
where cte2.nodes like (cte.nodes+ '_%'))

Answer 1

我对此有所了解，但我必须做出一些重大假设。 首先，我将您的数据放入表变量中：

DECLARE @tbl_nodess TABLE (nod1 VARCHAR(50), nod2 VARCHAR(50));
INSERT INTO @tbl_nodess VALUES('A','B'); 
INSERT INTO @tbl_nodess VALUES('B','C');
INSERT INTO @tbl_nodess VALUES('C','B');
INSERT INTO @tbl_nodess VALUES('D','E');
INSERT INTO @tbl_nodess VALUES('E','F');
INSERT INTO @tbl_nodess VALUES('G','H');
INSERT INTO @tbl_nodess VALUES('B','D');
INSERT INTO @tbl_nodess VALUES('D','A');
INSERT INTO @tbl_nodess VALUES('I','J');
INSERT INTO @tbl_nodess VALUES('J','K');
INSERT INTO @tbl_nodess VALUES('K','L');
INSERT INTO @tbl_nodess VALUES('L','J');

然后我写了一个递归查询：

WITH x AS (
SELECT
t.nod1,
t.nod2,
CONVERT(VARCHAR(500), t.nod1 + '-' + t.nod2) AS accumulator,
0 AS done
FROM
@tbl_nodess t
UNION ALL
SELECT
t.nod1,
t.nod2,
CONVERT(VARCHAR(500), x.accumulator + '-' + t.nod2) AS accumulator,
CASE WHEN CHARINDEX(t.nod2, x.accumulator) != 0 THEN 1 ELSE 0 END AS done
FROM
x
INNER JOIN @tbl_nodess t ON t.nod1 = x.nod2
WHERE
x.done = 0)
SELECT
accumulator AS self_ref_nodes
FROM
x
WHERE
done = 1;

那么这是如何工作的呢？

我有递归查询，使用"X - Y -> Y - Z"规则。 我还积累链，从 nod1 - nod2 开始，然后在找到匹配项时添加任何"新"nod2。

如果我碰到一个循环，那么我想停下来，但我不想立即停下来。 我想在下一个循环中停止，所以我使用"完成"来处理这个问题。 我设置了它，然后在下次通过递归运行时将其设置为 1 时停止。

这可以防止循环引用，但如果有更多的元素，那么最终可能会达到 100 的限制。

还值得注意的是，我发现很多自引用循环，比您的示例中更多：

L-J-K-L
K-L-J-K
J-K-L-J
I-J-K-L-J
D-A-B-D
D-A-B-C-B
B-D-A-B
C-B-C
C-B-D-A-B
B-C-B
A-B-D-A
A-B-C-B