How to Find All Connected Subgraphs of an Undirected Graph

Problem:

Given a table with two columns containing identifiers, find all groups of identifiers that are connected to each Outros Tabela:

id identificer1 identificer2 1 a c 2 b &] g h 5 5 (a, c, g, h, l) c 1 (a, c, g, h, l) (b, d, f, j) e

		f
	4	C
		6
9	L	H
	desivreado Output:
identificier	gr_id	gr.members
	a	1
	&&]

3 (e, k)

k 3 (e, k) l 1 &&] com Cte_idents COMO ( Selecione Ident1 como Ident De @t UNIÃO Selecione Ident2 como Ident De @t ) , Cte_pairs COMO ( Selecione Ident1, Ident2 De @t Onde ident1 & lt; & gt; Ident2 UNIÃO Selecione Ident2 como Ident1, Ident1 como Ident2 De @t Onde ident1 & lt; & gt; Ident2 ) , Cte_recursive COMO ( Selecione Elenco (cte_idents.ident como varchar (8000)) como ancorente , Ident1 , Ident2 , Elenco (',' ident1 ',' ident2 ',' como varchar (8000)) como identificação , 1 como lvl DE Cte_pairs Junção interna CTE_IDENTS em cte_idents.ident = cte_pairs.ident1 Union All Selecione Cte_recursive.anchorident , Cte_pairs.ident1 , Cte_pairs.ident2 . , Cte_recursive.lvl 1 como lvl DE Cte_pairs Junção interna cte_recursive em cte_recursive.ident2 = cte_pairs.ident1 ONDE Cte_recursive.identPath não como elenco ('%,' cte_pairs.ide2 ',%' como varchar (8000)) ) , Cte_recursionResult COMO ( Selecione Anchorident, Ident1, Ident2 De cte_recursive ) , Cte_cleanResult COMO ( Selecione ancorente, ident1 como identificação De cte_recursionResult UNIÃO Selecione Anchorident, ident2 como identificador De cte_recursionResult ) Selecione Cte_idents.ident , Caso em que ca_data.xml_value é nulo Então cte_idents.ident else Ca_Data.xml_value final como grupo , Dense_rank () sobre (Ordem por Caso quando Ca_Data.xml_value é nulo Então cte_idents.ident else Ca_Data.xml_value final ) Como grupo DE Cte_idents Cross Apply ( Selecione cte_cleanResult.ident ',' De cte_cleanResult Onde cte_cleanResult.anchorident = cte_idents.ident Ordem por cte_cleanResult.ident para XML Path (''), tipo ) Como ca_xml (xml_value) Cross Apply ( Selecione Ca_xml.xml_value.value ('.', 'Nvarchar (max)') ) Como ca_data (xml_value) ONDE Cte_idents.ident não é nulo Ordem por ident; Output: identifier gr_id gr.members a 1 (a, c, g, h, l) b 2 1 d e 3 (e, k) f &&] g 1 (a, c, g, h, l) h &&] &&] k 3

i	4	(i)
	solução:	Seguir a Query usa uma única consulta recursiva para encontrar todos os sub -regrafos conectados:
	&&&]	c
(a, c, g, h, l)		[&] &]
i	4	(i)
j	2

(e, k)

l

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 
        , Ident1
        , 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   ',' AS varchar(8000)) AS IdentPath
        , 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, Ident1, 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;

z 5

(z) explicação:

		Query usa um CTE recursivo para encontrar todos os caminhos no gráfico que seguem as bordas definidas na tabela cte_pairs. O caminho XML e o cruzamento se aplicam para concatenar os identificadores conectados para cada grupo.