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 Andere.

Beispiel Table:

ID	Identifier1	Identifier2
1	a	c
2	b	f
3	a	g
4	c	h
5	b	j
6	d	f
7	e	k

[] [] [] wurden dannömer 9 l h gewünscht Ausgabe: identifier gr_id gr.members a 1 (a, c, g, h, l) b 2 (b ,d,f,j)c1(a,c,g,h,l)d2(b,d,f,j)e3(e,k)f 2 (b, d, f, j) g 1 (a, g, h, 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;

1 k 3 (e, k) l 1 (a, g, h, l) werden &&] i 4 (i) Lösung: sample Ausgabe: identifier gr_id gr.members

		H
. &&]
die Die folgende Abfrage verwendet eine einzelne rekursiv Cte_idents ALS ( Wählen Sie Ident1 als Ident Von @t UNION Wählen Sie Ident2 als Ident Von @t ) , Cte_pairs ALS ( Wählen Sie Ident1, Ident2 Von @t Wo ident1 & lt; & gt; Ident2 UNION Wählen Sie Ident2 als Ident1, Ident1 als Ident2 aus Von @t Wo ident1 & lt; & gt; Ident2 ) , Cte_recursive ALS ( WÄHLEN Cast (cte_idents.ident als varchar (8000)) als Anchorident , Ident1 , Ident2 , Cast (',' ident1 ',' ident2 ',' as varchar (8000)) als Identpath , 1 als LVL AUS Cte_pairs Innere Join cte_idents auf cte_idents.ident = cte_pairs.ident1 Gewerkschaft alle WÄHLEN Cte_recursive.anchorid , Cte_pairs.ident1 , Cte_pairs.ident2 , Cast (cte_recursive.identath cte_pairs.ident2 ',' als Varchar (8000)) als Identpath , Cte_recursive.lvl 1 als LVL AUS Cte_pairs Innere Join CTE_RECURSIVE auf CTE_RECURIVE.IDID2 = CTE_PAIRS.IDID1 WO CTE_RECURIVE. Identpath nicht wie Cast ('%,' cte_pairs.ident2 ',%' als Varchar (8000)) ) , CTE_RecursionResult ALS ( Wählen Sie Anchorident, Ident1, Ident2 aus Von cte_recursive ) , Cte_cleanResult ALS ( Wählen Sie Anchorident, Ident1 als Ident Von CTE_RecursionResult UNION Wählen Sie Anchorident, Ident2 als Ident Von CTE_RecursionResult ) WÄHLEN Cte_idents.ident , Fall, wenn ca_data.xml_value null ist Dann cte_idents.ident sonst ca_data.xml_value endet als Gruppenmitglieder , Dichter_rank () über (bestellen by Fall, wenn ca_data.xml_value null ist Dann cte_idents.ident sonst ca_data.xml_value End ) Als gruppId AUS Cte_idents Kreuzbewerb ( Wählen Sie CTE_CleanResult.ident ',', ',' Von CTE_CleanResult Wo cte_cleanResult.anchorident = cte_idents.ident Bestellung von CTE_CleanResult.ident für XML Path (''), Typ, Typ ) Als ca_xml (xml_value) Kreuzbewerb ( Wählen Sie ca_xml.xml_value.value ('.', 'Nvarchar (max)') ) Als ca_data (xml_value) WO Cte_idents.ident ist nicht null Bestellen von ident;

• [&anzen haben sich ]
• a
• 1

(a,c,g,h,l)

b2

(b,d,f,j)

c

1(a,c,g,h,l)

d2(b,d,f,j) e 3 (e, k) f 2 (b, d, f, j) ; &&]

g	1	(a,c,g,h,l)
h	1	(a,c,g,h,l)

i

4

(i) k3(e,k)l1(a,c,g,h,l) z 5 (z) Erklärung: die Query verwendet ein rekursives CTE, um alle Pfade in der Grafik zu finden, die den in der Tabelle cte_pairs definierten Kanten folgen. XML -Pfad und Cross gelten für die Verkettung der verbundenen Kennungen für jede Gruppe. dense_rank () werden jeder Gruppe eindeutige Gruppen -IDs zugewiesen.

j	2	(b, d, f, j)