java 无向图子图_sql – 如何查找无向图的所有连接子图

这是一个不使用游标的变体,但使用单个递归查询.

本质上,它将数据视为图中的边,并递归遍历图的所有边,在检测到循环时停止.然后它将所有找到的循环放入组中,并为每个组分配一个数字.

请参阅下面有关其工作原理的详细说明.我建议您运行查询CTE-by-CTE并检查每个中间结果以了解它的作用.

样品1

DECLARE @T TABLE (ID int,Ident1 char(1),Ident2 char(1));

INSERT INTO @T (ID,Ident1,Ident2) VALUES

(1,'a','a'),(2,'b','b'),(3,'c',(4,(5,'c');

样本2

我添加了另一行z值,以使多行具有不成对的值.

DECLARE @T TABLE (ID int,(1,'c'),'f'),'g'),'h'),'j'),(6,'d',(7,'e','k'),(8,'i',NULL),(88,'z','z'),(9,'l','h');

样本3

DECLARE @T TABLE (ID int,(10,(11,(12,(13,'l'),(14,(15,(16,'m'),(17,(18,(19,(20,(21,(22,NULL);

询问

WITH

CTE_Idents

AS

(

SELECT Ident1 AS Ident

FROM @T

UNION

SELECT Ident2 AS Ident

FROM @T

),CTE_Pairs

AS

(

SELECT Ident1,Ident2

FROM @T

WHERE Ident1 <> Ident2

UNION

SELECT Ident2 AS Ident1,Ident1 AS Ident2

FROM @T

WHERE Ident1 <> Ident2

),CTE_Recursive

AS

(

SELECT

CAST(CTE_Idents.Ident AS varchar(8000)) AS AnchorIdent,Ident2,CAST(',' + Ident1 + ',' + Ident2 + ',' AS varchar(8000)) AS IdentPath,1 AS Lvl

FROM

CTE_Pairs

INNER JOIN CTE_Idents ON CTE_Idents.Ident = CTE_Pairs.Ident1

UNION ALL

SELECT

CTE_Recursive.AnchorIdent,CTE_Pairs.Ident1,CTE_Pairs.Ident2,CAST(CTE_Recursive.IdentPath + CTE_Pairs.Ident2 + ',CTE_Recursive.Lvl + 1 AS Lvl

FROM

CTE_Pairs

INNER JOIN CTE_Recursive ON CTE_Recursive.Ident2 = CTE_Pairs.Ident1

WHERE

CTE_Recursive.IdentPath NOT LIKE CAST('%,' + CTE_Pairs.Ident2 + ',%' AS varchar(8000))

),CTE_RecursionResult

AS

(

SELECT AnchorIdent,Ident2

FROM CTE_Recursive

),CTE_CleanResult

AS

(

SELECT AnchorIdent,Ident1 AS Ident

FROM CTE_RecursionResult

UNION

SELECT AnchorIdent,Ident2 AS Ident

FROM CTE_RecursionResult

)

SELECT

CTE_Idents.Ident,CASE WHEN CA_Data.XML_Value IS NULL

THEN CTE_Idents.Ident ELSE CA_Data.XML_Value END AS GroupMembers,DENSE_RANK() OVER(ORDER BY

CASE WHEN CA_Data.XML_Value IS NULL

THEN CTE_Idents.Ident ELSE CA_Data.XML_Value END

) AS GroupID

FROM

CTE_Idents

CROSS APPLY

(

SELECT CTE_CleanResult.Ident+','

FROM CTE_CleanResult

WHERE CTE_CleanResult.AnchorIdent = CTE_Idents.Ident

ORDER BY CTE_CleanResult.Ident FOR XML PATH(''),TYPE

) AS CA_XML(XML_Value)

CROSS APPLY

(

SELECT CA_XML.XML_Value.value('.','NVARCHAR(MAX)')

) AS CA_Data(XML_Value)

WHERE

CTE_Idents.Ident IS NOT NULL

ORDER BY Ident;

结果1

+-------+--------------+---------+

| Ident | GroupMembers | GroupID |

+-------+--------------+---------+

| a | a,b,| 1 |

| b | a,| 1 |

| c | a,| 1 |

+-------+--------------+---------+

结果2

+-------+--------------+---------+

| Ident | GroupMembers | GroupID |

+-------+--------------+---------+

| a | a,l,| 1 |

| b | b,j,| 2 |

| c | a,| 1 |

| d | b,| 2 |

| e | e,k,| 3 |

| f | b,| 2 |

| g | a,| 1 |

| h | a,| 1 |

| i | i | 4 |

| j | b,| 2 |

| k | e,| 3 |

| l | a,| 1 |

| z | z | 5 |

+-------+--------------+---------+

结果3

+-------+--------------------------+---------+

| Ident | GroupMembers | GroupID |

+-------+--------------------------+---------+

| a | a,e,m,| 1 |

| d | a,| 1 |

| e | a,| 1 |

| f | a,| 1 |

| g | a,| 1 |

| j | a,| 1 |

| k | a,| 1 |

| l | a,| 1 |

| m | a,| 1 |

+-------+--------------------------+---------+

这个怎么运作

我将使用第二组样本数据进行解释.

CTE_Idents

CTE_Idents给出了Ident1和Ident2列中出现的所有标识符的列表.

由于它们可以按任何顺序出现,因此我们将两个列联合起来. UNION还删除任何重复项.

+-------+

| Ident |

+-------+

| NULL |

| a |

| b |

| c |

| d |

| e |

| f |

| g |

| h |

| i |

| j |

| k |

| l |

| z |

+-------+

CTE_Pairs

CTE_Pairs给出了两个方向上图形的所有边的列表.同样,UNION用于删除任何重复项.

+--------+--------+

| Ident1 | Ident2 |

+--------+--------+

| a | c |

| a | g |

| b | f |

| b | j |

| c | a |

| c | h |

| d | f |

| e | k |

| f | b |

| f | d |

| g | a |

| h | c |

| h | l |

| j | b |

| k | e |

| l | h |

+--------+--------+

CTE_Recursive

CTE_Recursive是查询的主要部分,它从每个唯一标识符开始递归遍历图形.

这些起始行由UNION ALL的第一部分生成.

UNION ALL的第二部分以递归方式连接自身,将Ident2链接到Ident1.

由于我们预先制作了CTE_Pairs,所有边都写在两个方向上,我们总是只能将Ident2链接到Ident1,我们将获得图中的所有路径.

同时,查询构建IdentPath – 一串到目前为止已遍历的逗号分隔的标识符.

它在WHERE过滤器中使用：

CTE_Recursive.IdentPath NOT LIKE CAST('%,%' AS varchar(8000))

一旦我们遇到之前包含在Path中的Identifier,递归就会在连接节点列表耗尽时停止.

AnchorIdent是递归的起始标识符,稍后将用于对结果进行分组.

Lvl并没有真正使用,我将其包含在内以便更好地了解正在发生的事情.

+-------------+--------+--------+-------------+-----+

| AnchorIdent | Ident1 | Ident2 | IdentPath | Lvl |

+-------------+--------+--------+-------------+-----+

| a | a | c |,a,| 1 |

| a | a | g |,| 1 |

| b | b | f |,| 1 |

| b | b | j |,| 1 |

| c | c | a |,| 1 |

| c | c | h |,| 1 |

| d | d | f |,| 1 |

| e | e | k |,| 1 |

| f | f | b |,| 1 |

| f | f | d |,| 1 |

| g | g | a |,| 1 |

| h | h | c |,| 1 |

| h | h | l |,| 1 |

| j | j | b |,| 1 |

| k | k | e |,| 1 |

| l | l | h |,| 1 |

| l | h | c |,| 2 |

| l | c | a |,| 3 |

| l | a | g |,| 4 |

| j | b | f |,| 2 |

| j | f | d |,| 3 |

| h | c | a |,| 2 |

| h | a | g |,| 3 |

| g | a | c |,| 2 |

| g | c | h |,| 3 |

| g | h | l |,| 4 |

| f | b | j |,| 2 |

| d | f | b |,| 2 |

| d | b | j |,| 3 |

| c | h | l |,| 2 |

| c | a | g |,| 2 |

| b | f | d |,| 2 |

| a | c | h |,| 2 |

| a | h | l |,| 3 |

+-------------+--------+--------+-------------+-----+

CTE_CleanResult

CTE_CleanResult仅从CTE_Recursive中保留相关部分,并再次使用UNION合并Ident1和Ident2.

+-------------+-------+

| AnchorIdent | Ident |

+-------------+-------+

| a | a |

| a | c |

| a | g |

| a | h |

| a | l |

| b | b |

| b | d |

| b | f |

| b | j |

| c | a |

| c | c |

| c | g |

| c | h |

| c | l |

| d | b |

| d | d |

| d | f |

| d | j |

| e | e |

| e | k |

| f | b |

| f | d |

| f | f |

| f | j |

| g | a |

| g | c |

| g | g |

| g | h |

| g | l |

| h | a |

| h | c |

| h | g |

| h | h |

| h | l |

| j | b |

| j | d |

| j | f |

| j | j |

| k | e |

| k | k |

| l | a |

| l | c |

| l | g |

| l | h |

| l | l |

+-------------+-------+

最后的选择

现在我们需要为每个AnchorIdent构建一串逗号分隔的Ident值.用FOR XML交叉应用就可以了.DENSE_RANK()计算每个AnchorIdent的GroupID号.