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

使用SQL Server在数据中查找循环

sql-server-2012 tsql sql-server

MAK · 技术社区 · 6 年前

我有以下数据来找到循环。

表:

CREATE TABLE tblLoop
(
    person1 varchar(20),
    person2 varchar(20)
);

INSERT INTO tblLoop VALUES('A','B'),('A','C'),('A','D'),
                          ('B','E'),('B','F'),
                          ('D','G'),('D','H'),
                          ('F','i'),
                          ('G','J'),
                          ('i','A'),
                          ('J','D');

编辑:添加了更多值

INSERT INTO tblLoop VALUES('X','Y'),('X','Z'),('Z','X'),('Y','W');

记录看起来像:

注: :有可能有多棵这样的树,我们需要找到所有树的循环数据。

要求:我需要找到形成循环的人。对于给定数据中的一个例子,我们发现了2个循环:

循环1: A 连接到 B 连接到 F 连接到 i 连接到 A. .

循环2: A. 连接到 D 连接到 G 连接到 J 连接到 D .

预期结果:

LoopFound
--------------------
A->B->F->i->A
A->D->G->J->D
X->Z->X

我的尝试:

;WITH CTE_Loop AS 
(
    SELECT  t.Person1,t.Person2,
            CONVERT(VARCHAR(500), t.Person1 + '->' + t.Person2) AS [Loop],
            0 AS FoundFlag
    FROM tblLoop t 
    UNION ALL
    SELECT t.Person1,t.Person2,
            CONVERT(VARCHAR(500), cte.[Loop] +'->'+t.Person2) AS [Loop],
            CASE WHEN CHARINDEX(t.Person2, cte.[Loop]) != 0 THEN 1 ELSE 0 END AS FoundFlag
    FROM CTE_Loop cte
    INNER JOIN tblLoop t ON t.Person1 = cte.Person2
    WHERE cte.FoundFlag = 0  AND t.Person1 <> '-' AND t.Person2 <> '-'
)
SELECT [Loop] AS LoopFound
FROM CTE_Loop 
WHERE FoundFlag = 1 
GROUP BY [Loop];

0 回复 | 直到 6 年前

EmreB. 6 年前

SELECT  
   CONCAT_WS('->',t1.person1,t1.person2,t2.person2 ,t3.person2,t4.person2) 
FROM tblLoop t1
left join tblLoop t2 on t1.person2 = t2.person1
left join tblLoop t3 on t2.person2 = t3.person1
left join tblLoop t4 on t3.person2 = t4.person1
where t1.person1 = 'A'AND t4.person2 IS NOT NULL

lptr 6 年前

你可以开始&;循环结束,假设存在某种层次结构(例如a在B之前,B在C之前等),并排除person2不显示为person1的行(即关系/链的末端)。。。

--get loops
select  distinct a.person1, b.person1, b.person2  --a.person1, b.person1 :: loop range(start-end)
from tblLoop as a
join tblLoop as b on a.person1 = b.person2 and a.person1 <= b.person1 --assume a hierarchy by name (A is on a higher/or equal level than B etc)
where exists(select * from tblLoop as d where d.person1 = a.person2) --exclude dead ends (person2 which is not person1);

..并将其注入cte。您可以计算出没有相同开始和循环的重叠和循环;结束。

(例如,A->D->G->J->D或D->G->J->D是一个循环?)

;WITH CTE_Loop AS 
(
    SELECT  t.Person1,t.Person2,
            CONVERT(VARCHAR(500), t.Person1 + '->' + t.Person2) AS [Loop],
            0 AS FoundFlag
    FROM tblLoop t 
    where t.person1 in 
    (
        select  distinct a.person1--, b.person1, b.person2  --a.person1, b.person1 :: loop range(start-end)
        from tblLoop as a
        join tblLoop as b on a.person1 = b.person2 and a.person1 <= b.person1 --assume a hierarchy by name (A is on a higher/or equal level than B etc)
        where exists(select * from tblLoop as d where d.person1 = a.person2) --exclude dead ends (person2 which is not person1)     
    )
    UNION ALL
    SELECT t.Person1,t.Person2,
            CONVERT(VARCHAR(500), cte.[Loop] +'->'+t.Person2) AS [Loop],
            CASE WHEN CHARINDEX(t.Person2, cte.[Loop]) != 0 THEN 1 ELSE 0 END AS FoundFlag
    FROM CTE_Loop cte
    INNER JOIN tblLoop t ON t.Person1 = cte.Person2
    WHERE cte.FoundFlag = 0  AND t.Person1 <> '-' AND t.Person2 <> '-'
)
SELECT [Loop] AS LoopFound
FROM CTE_Loop 
WHERE FoundFlag = 1 
GROUP BY [Loop];