这是一个不使用游标的变体,但使用单个递归查询.
本质上,它将数据视为图中的边,并递归遍历图的所有边,在检测到循环时停止.然后它将所有找到的循环放入组中,并为每个组分配一个数字.
请参阅下面有关其工作原理的详细说明.我建议您运行查询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号.