在Oracle SQL中识别传递匹配记录并生成目标结果的方法

源表（SOURCE） #

目标表（TARGET） #

需求说明 #

需要识别SOURCE表中的传递匹配记录（即属于同一连通组的节点，例如1关联2、2关联4、4关联6、6关联5，那么1、2、3、4、5、6属于同一组），并为每个组分配该组内最小的Rowid_object作为统一匹配值，生成目标表结构。

解决方案（Oracle SQL） #

利用递归遍历结合分析函数实现连通分量识别，代码如下：

WITH all_nodes AS (
    -- 收集所有出现过的节点（包含双向关联的ID）
    SELECT Rowid_object AS id FROM SOURCE
    UNION
    SELECT Rowid_object_matched AS id FROM SOURCE
),
hierarchy AS (
    -- 递归遍历所有关联节点，标记每个节点的根节点
    SELECT
        id,
        CONNECT_BY_ROOT(id) AS root_id
    FROM all_nodes
    LEFT JOIN SOURCE s ON all_nodes.id = s.Rowid_object
    CONNECT BY NOCYCLE PRIOR s.Rowid_object_matched = id
),
group_min AS (
    -- 为每个节点确定所在组的最小ID
    SELECT
        id,
        MIN(root_id) OVER (PARTITION BY id) AS min_group_id
    FROM hierarchy
)
-- 去重并排序得到最终结果
SELECT DISTINCT
    id AS Rowid_object,
    min_group_id AS Rowid_object_matched
FROM group_min
ORDER BY Rowid_object;

逻辑解释 #

1. all_nodes：合并SOURCE表中所有作为Rowid_object和Rowid_object_matched的ID，确保没有遗漏任何需要处理的节点。
2. hierarchy：通过CONNECT BY递归遍历节点间的关联关系，CONNECT_BY_ROOT(id)记录每个节点遍历的起始根节点，NOCYCLE避免循环关联导致的报错。
3. group_min：对每个节点，在其所有可能的根节点中取最小值，作为该组的统一匹配ID，确保组内所有节点映射到同一个最小ID。
4. 最后通过DISTINCT去重并排序，得到与目标表一致的结果。

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