代码之家 › 专栏 › 技术社区 › MAK

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

sql-server-2012 tsql sql-server

MAK · 技术社区 · 6 年前

表

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

节点 A 提到 B , 提到 C , 提到 B

再次提到节点 B .

A 提到 B 提到 D , D 提到 A

节点 D A .

I 提到 J J 提到 K , L 我 提到

节点 我 再次提到节点 (已经在链中)。

查询:

;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+ '_%'))

0 回复 | 直到 6 年前

Richard Hansell 6 年前

我对这个有点了解,但我必须做出一些重要的假设。首先,我将您的数据放入一个表变量:

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;

这是怎么回事?

如果我碰到一个循环,我想停下来,但我不想马上停下来。我想在下一个循环中停止,所以我使用“done”来处理这个问题。我设置了它,然后在下一次递归运行中将它设置为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