Как найти все связанные подграфы неисправенного графика

задача:

дали таблицу с двумя столбцами, содержащими идентификаторы, найдите все группы идентификаторов, которые подключены к каждую. Другое.

пример Таблица:

g 4 c h 5 6 7 9 идентификатор

id	идентификатор1	идентификатор 2
1	a	c
2	b	f
d	f
e	k
l	h
Deal Вывод:
gr_id	gr.members

a

1

(a, c, g, h, l) (b , d, f, j) c 1 (a, c, g, h, l) d 2 (b, d, f, j) g 1 (a, c, g, h, l) h 1 (a, c, g, h, l) j 2 (b, d, f, j) k 3 (e, k) l 1 (a, c, h, h, l) 4 решение: Следующий запрос использует один рекурсивный запрос, чтобы найти все подключенные подграфы: образец Output: идентификатор gr_id

b	2
	d	(b, d, f, j)
e	3	(e, k)
		i
(i)
		с Cte_idents КАК ( Выберите Ident1 в качестве идентификации От @t Союз Выберите Ident2 в качестве идентификации От @t ) , Cte_pairs КАК ( Выберите Iment1, Ident2 От @t Где Ident1 & lt; & gt; Ident2 Союз Выберите Iment2 как idEn1, idet1 как идентификация2 От @t Где Ident1 & lt; & gt; Ident2 ) , Cte_recursive КАК ( ВЫБИРАТЬ CAST (CTE_IDENTS.IDED AS VARCHAR (8000)) в качестве якорного , Ident1 , Ident2 , CAST (',' Ident1 ',' Ident2 ', как varchar (8000)) как iDepath 1 как lvl ОТ Cte_pairs Внутреннее присоединение CTE_IDENTS на cte_idents.ident = cte_pairs.ident1 Союз все ВЫБИРАТЬ Cte_recursive.anchorident , Cte_pairs.ident1 , Cte_pairs.ident2 , Cast (cte_recursive.identPath Cte_pairs.ident2 ',' как varchar (8000)) как identpath , Cte_recursive.lvl 1 как lvl ОТ Cte_pairs Внутреннее соединение CTE_Recursive на cte_recursive.ident2 = cte_pairs.ident1 ГДЕ Cte_recursive.identpath не похож на Cast ('%,' cte_pairs.ident2 ',%' as archar (8000)) ) , Cte_recursionresult КАК ( Выберите Anchorident, Ident1, Ident2 От cte_recursive ) , Cte_cleanresult КАК ( Выберите Anchorident, Ident1 в качестве идентификации От cte_recursionresult Союз Выберите Anchorident, Ident2 в качестве идентификации От cte_recursionresult ) ВЫБИРАТЬ Cte_idents.ided , Случай, когда ca_data.xml_value null Тогда cte_idents.ide else ca_data.xml_value end as Goursmembers , Dense_rank () Over (заказ за Случай, когда CA_DATA.xml_value null Тогда cte_idents.ide else ca_data.xml_value end ) Как группа ОТ Cte_idents Крест применить ( Выберите cte_cleanresult.ident ',' От cte_cleanresult Где cte_cleanresult.anchorident = cte_idents.ided Заказ от cte_cleanresult.ident для xml path (''), тип ) Как ca_xml (xml_value) Крест применить ( Выберите ca_xml.xml_value.value ('.', 'Nvarchar (max)') ) Как ca_data (xml_value) ГДЕ CTE_IDENTS.IDED не нулевой Заказ по идентификации;

gr.members

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

(a, c, g, h, l)

b c 1 (a, c, g, h, l) d 2 (b, d, f, j) e 3 (e, k) f 2 (b, d, f, j) (a, c, g, h, l) (a, c, h, l) (i) (b, d, f, j) k 3 (e, k) l 1 (a, c, g, h, l) z 5 (z) объяснение: Query использует рекурсивный CTE, чтобы найти все пути на графике, которые следуют по краям, определенным в таблице CTE_PARAIRS. Путь XML и крест применимы к объединенным идентификаторам для каждой группы. dense_rank () используется для назначения уникальных идентификаторов группы каждой группе. ]

2	(b, d, f, j)
	]
	g	1
	h	1
	i	4
	j	2