为多对多关联表中所有关联记录分配Group_ID

我数据库中有三张表：

• Collateral（抵押物表）
• Loans（贷款表）
• Loan_Collateral_link（贷款与抵押物的多对多关联表）

抵押物可关联1个或多个贷款，一个贷款也可关联多个抵押物。需要生成一个结果集，将所有通过贷款或抵押物间接关联的贷款与抵押物归为同一组。

表数据 #

Loans表 #

Collaterals表 #

Loan_Collateral_link表 #

预期结果 #

我的尝试 #

考虑用循环遍历Loan_Collateral_Link记录，把关联项插入临时表，再遍历临时表查找所有关联记录，但逻辑上容易陷入无限循环，初步思路代码如下：

--ForEach Loan_Collateral_Link Loan;
    --If not exists - Insert LOAN_ID into TempTable
    --If not exist - Insert COL_ID into TempTable
        --ForEach TempTable COL_ID;
            --Select * FROM Loan_Collateral_Link where Loan_ID or Col_ID matches
                --> If not exists - Insert LOAN_ID / COL_ID into TempTable

也考虑按贷款逐个处理，添加关联抵押物后再查找关联贷款，但不确定循环次数，是否需要通过标记持续循环直到所有关联项都被处理？

这类关联分组本质是图的连通分量问题——贷款和抵押物是图的节点，关联关系是节点间的边，我们需要找出所有相互连通的节点组。用递归CTE（Common Table Expression）可以高效解决，无需手动写循环，从根源避免无限循环问题。

完整SQL实现 #

WITH AllNodes AS (
    -- 合并所有贷款和抵押物节点
    SELECT ID AS item_id FROM Loans
    UNION
    SELECT ID AS item_id FROM Collateral
),
Edges AS (
    -- 构建双向关联边：贷款↔抵押物
    SELECT Loan_ID AS from_node, Collateral_ID AS to_node FROM Loan_Collateral_link
    UNION ALL
    SELECT Collateral_ID AS from_node, Loan_ID AS to_node FROM Loan_Collateral_link
),
ConnectedComponents AS (
    -- 锚点成员：初始化每个节点为独立组件
    SELECT 
        item_id AS node,
        item_id AS root
    FROM AllNodes
    UNION ALL
    -- 递归成员：扩展连通节点，将关联节点归入同一组件
    SELECT 
        e.to_node AS node,
        cc.root AS root
    FROM ConnectedComponents cc
    JOIN Edges e ON cc.node = e.from_node
    WHERE e.to_node NOT IN (SELECT node FROM ConnectedComponents)
),
GroupAssignments AS (
    -- 给每个连通分量分配统一的GroupID
    SELECT 
        root,
        CONCAT('Group', DENSE_RANK() OVER (ORDER BY root)) AS group_id
    FROM ConnectedComponents
    GROUP BY root
)
-- 生成最终分组结果
SELECT 
    ga.group_id,
    cc.node AS item_id
FROM ConnectedComponents cc
JOIN GroupAssignments ga ON cc.root = ga.root
ORDER BY ga.group_id, cc.node;

代码说明 #

1. AllNodes：将贷款和抵押物的ID合并为统一的节点集合，确保没有遗漏任何节点。
2. Edges：把单向的关联关系转换成双向边，保证可以从贷款找到抵押物，也能从抵押物反向找到关联的其他贷款。
3. ConnectedComponents：递归遍历所有连通节点，用root字段标记同一组的根节点，确保所有间接关联的节点都归入同一根节点下。
4. GroupAssignments：给每个根节点分配GroupX格式的组ID，让分组结果更直观。
5. 最终关联查询得到符合预期的分组结果。

为什么不用循环？ #

手动循环容易出现重复插入、无限循环的问题，递归CTE由数据库自动处理遍历逻辑，直到没有新节点可以加入，性能更优且逻辑更简洁。

内容的提问来源于stack exchange，提问作者Karel-Jan Misseghers