使用递归CTE基于双向关联关系分组产品的问题排查

错误原因 #

递归CTE陷入无限循环的核心原因是双向关联的无限制递归：

• 产品关联是双向结构（比如A关联B，B也关联A），递归过程中没有限制已访问的节点，导致CTE反复在两个节点之间来回调用，超出SQL的递归深度限制报错。
• 哪怕只有两行测试数据（如(ProdA, ProdB)和(ProdB, ProdA)），递归也会无限交替触发这两条记录，无法终止。

临时表b的必要性 #

是否需要显式定义临时表b，取决于它的作用：

• 如果临时表b是用来去重双向关联关系（比如只保留Product1 < Product2的单向记录），或者存储已访问节点集合，那是完全必要的——它能从根源上切断双向循环的触发条件。
• 如果只是冗余的中间表，没有处理关联关系或追踪访问状态，那可以省略，但此时必须在递归逻辑中加入防循环的判断。

优化建议 #

1. 预处理关联关系，消除双向循环 #

先对原始Product表的关联数据去重，生成单向无环的关联对，避免递归时来回触发：

-- 生成单向关联临时表，只保留ProductID1 < ProductID2的记录
WITH DistinctRelations AS (
    SELECT DISTINCT
        CASE WHEN ProductID1 < ProductID2 THEN ProductID1 ELSE ProductID2 END AS Source,
        CASE WHEN ProductID1 < ProductID2 THEN ProductID2 ELSE ProductID1 END AS Target
    FROM Product
)

2. 递归CTE中加入已访问节点追踪 #

在递归过程中记录已经处理过的产品ID，每次递归只处理未访问的节点，避免循环：

WITH DistinctRelations AS (
    SELECT DISTINCT
        CASE WHEN ProductID1 < ProductID2 THEN ProductID1 ELSE ProductID2 END AS Source,
        CASE WHEN ProductID1 < ProductID2 THEN ProductID2 ELSE ProductID1 END AS Target
    FROM Product
),
RecursiveGroups AS (
    -- 锚点成员：每个产品作为初始节点，记录已访问集合
    SELECT 
        ProductID AS CurrentID,
        ProductID AS GroupID,
        CAST(',' + CAST(ProductID AS VARCHAR(MAX)) + ',' AS VARCHAR(MAX)) AS Visited
    FROM (SELECT DISTINCT ProductID FROM (SELECT ProductID1 AS ProductID FROM Product UNION SELECT ProductID2 AS ProductID FROM Product) AS AllProducts) AS P
    UNION ALL
    -- 递归成员：只关联未访问过的节点
    SELECT 
        dr.Target AS CurrentID,
        rg.GroupID,
        rg.Visited + CAST(dr.Target AS VARCHAR(MAX)) + ',' AS Visited
    FROM RecursiveGroups rg
    JOIN DistinctRelations dr ON rg.CurrentID = dr.Source
    WHERE CHARINDEX(',' + CAST(dr.Target AS VARCHAR(MAX)) + ',', rg.Visited) = 0
)
-- 输出最终分组结果
SELECT DISTINCT CurrentID AS ProductID, GroupID FROM RecursiveGroups
ORDER BY GroupID, CurrentID;

3. 改用并查集（Union-Find）算法（更高效） #

对于这类连通分量分组问题，并查集算法比递归CTE性能更优，尤其适合大数据量场景：

-- 创建临时表存储所有产品
CREATE TABLE #Products (ProductID VARCHAR(50) PRIMARY KEY);
INSERT INTO #Products SELECT DISTINCT ProductID FROM (SELECT ProductID1 AS ProductID FROM Product UNION SELECT ProductID2 AS ProductID FROM Product) AS AllProducts;

-- 创建临时表存储父节点（并查集结构）
CREATE TABLE #UnionFind (ProductID VARCHAR(50) PRIMARY KEY, ParentID VARCHAR(50));
INSERT INTO #UnionFind SELECT ProductID, ProductID FROM #Products;

-- 合并关联节点
WHILE EXISTS (
    SELECT 1 FROM Product p
    JOIN #UnionFind uf1 ON p.ProductID1 = uf1.ProductID
    JOIN #UnionFind uf2 ON p.ProductID2 = uf2.ProductID
    WHERE uf1.ParentID != uf2.ParentID
)
BEGIN
    UPDATE uf2
    SET uf2.ParentID = uf1.ParentID
    FROM #UnionFind uf1
    JOIN #UnionFind uf2 ON uf2.ParentID = (SELECT ParentID FROM #UnionFind WHERE ProductID = (SELECT ProductID2 FROM Product WHERE ProductID1 = uf1.ProductID))
    WHERE uf1.ParentID != uf2.ParentID;
END

-- 输出分组结果
SELECT p.ProductID, uf.ParentID AS GroupID
FROM #Products p
JOIN #UnionFind uf ON p.ProductID = uf.ProductID
ORDER BY GroupID, ProductID;

-- 清理临时表
DROP TABLE #Products;
DROP TABLE #UnionFind;

4. 限制递归深度（应急方案） #

如果必须用递归CTE，可通过OPTION (MAXRECURSION n)指定最大递归深度，但这只是临时规避，不能从根源解决循环问题，不推荐作为长期方案：

-- 在CTE查询末尾添加
OPTION (MAXRECURSION 100)

内容的提问来源于stack exchange，提问作者woiya