Oracle如何用SQL/PLSQL实现关联记录递归查询及生成ID历史链

嘿，这俩问题其实是一套逻辑——都是处理Oracle里的层级关联ID数据，完全不用自己写循环折腾，用Oracle自带的层级查询工具就能轻松搞定！我给你一步步拆解：

问题1：查询某条记录的所有关联记录 #

要找到某个ID（比如ID_2）的所有上下游关联记录，核心是定位它所在的整个链条，这里有两种简单的实现方式：

方法1：双向CONNECT BY遍历 #

直接从目标ID出发，同时向上找父节点、向下找子节点：

SELECT DISTINCT
    CASE 
        WHEN CONNECT_BY_ISLEAF = 0 THEN Col_Old_ID 
        ELSE Col_New_ID 
    END AS Related_ID
FROM ID_MAPPING
START WITH Col_Old_ID = 'ID_2' OR Col_New_ID = 'ID_2'
CONNECT BY 
    PRIOR Col_New_ID = Col_Old_ID  -- 向下找子节点
    OR PRIOR Col_Old_ID = Col_New_ID; -- 向上找父节点

方法2：先找根节点再遍历全链条 #

如果链条是单向的（只有父→子，没有反向关联），可以先找到目标ID的根节点，再从根节点遍历所有子节点：

WITH Root_Find AS (
    -- 向上追溯找到目标ID的根节点
    SELECT 
        Col_Old_ID,
        CONNECT_BY_ROOT Col_Old_ID AS Root_ID
    FROM ID_MAPPING
    START WITH Col_Old_ID = 'ID_2'
    CONNECT BY Col_New_ID = PRIOR Col_Old_ID
)
-- 从根节点向下遍历所有关联节点
SELECT Col_Old_ID AS Related_ID
FROM ID_MAPPING
START WITH Col_Old_ID = (SELECT Root_ID FROM Root_Find WHERE ROWNUM = 1)
CONNECT BY PRIOR Col_New_ID = Col_Old_ID
UNION ALL
-- 加上链条的最后一个叶子节点（不在Col_Old_ID里的节点）
SELECT Col_New_ID AS Related_ID
FROM ID_MAPPING
WHERE Col_New_ID NOT IN (SELECT Col_Old_ID FROM ID_MAPPING)
START WITH Col_Old_ID = (SELECT Root_ID FROM Root_Find WHERE ROWNUM = 1)
CONNECT BY PRIOR Col_New_ID = Col_Old_ID;

问题2：批量生成所有节点的完整历史链 #

要给每个ID都附上它所在链条的完整历史串，用递归CTE（Oracle 11gR2及以上支持）最直观，逻辑清晰好维护：

WITH Recursive_History AS (
    -- 锚点：找到所有链条的根节点（没有父节点的ID）
    SELECT 
        Col_Old_ID AS Current_ID,
        Col_Old_ID AS History,
        Col_New_ID AS Next_ID,
        1 AS Level_Order
    FROM ID_MAPPING
    WHERE Col_Old_ID NOT IN (SELECT Col_New_ID FROM ID_MAPPING)
    
    UNION ALL
    
    -- 递归：向下遍历子节点，拼接历史串
    SELECT 
        rm.Col_New_ID AS Current_ID,
        rh.History || ',' || rm.Col_New_ID AS History,
        rm.Col_New_ID AS Next_ID,
        rh.Level_Order + 1 AS Level_Order
    FROM Recursive_History rh
    JOIN ID_MAPPING rm ON rh.Next_ID = rm.Col_Old_ID
),
Full_Chains AS (
    -- 把每个完整的历史串拆分成单个ID，让每个ID对应整个链条
    SELECT 
        History,
        REGEXP_SUBSTR(History, '[^,]+', 1, LEVEL) AS ID
    FROM Recursive_History
    -- 只取叶子节点的完整历史串（因为叶子节点的History是最长的）
    WHERE Next_ID NOT IN (SELECT Col_Old_ID FROM ID_MAPPING)
    CONNECT BY 
        LEVEL <= REGEXP_COUNT(History, ',') + 1
        AND PRIOR History = History
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免递归循环
)
-- 最终结果
SELECT 
    ID,
    History
FROM Full_Chains
ORDER BY ID;

如果你的Oracle版本低于11gR2，没法用递归CTE，也可以用CONNECT BY结合LISTAGG来实现，核心思路是先给每个节点标记所属的根节点，再按根节点聚合生成历史串：

WITH All_Nodes AS (
    -- 获取所有节点及其所属的根节点
    SELECT 
        Col_Old_ID AS ID,
        CONNECT_BY_ROOT Col_Old_ID AS Root_ID,
        LEVEL AS Node_Level
    FROM ID_MAPPING
    CONNECT BY PRIOR Col_New_ID = Col_Old_ID
    
    UNION ALL
    
    SELECT 
        Col_New_ID AS ID,
        CONNECT_BY_ROOT Col_Old_ID AS Root_ID,
        LEVEL + 1 AS Node_Level
    FROM ID_MAPPING
    WHERE Col_New_ID NOT IN (SELECT Col_Old_ID FROM ID_MAPPING)
    CONNECT BY PRIOR Col_New_ID = Col_Old_ID
),
Full_Histories AS (
    -- 按根节点聚合，生成完整历史串（按层级排序保证顺序正确）
    SELECT 
        Root_ID,
        LISTAGG(ID, ',') WITHIN GROUP (ORDER BY Node_Level) AS History
    FROM All_Nodes
    GROUP BY Root_ID
)
-- 关联每个节点到对应的历史串
SELECT 
    an.ID,
    fh.History
FROM All_Nodes an
JOIN Full_Histories fh ON an.Root_ID = fh.Root_ID
ORDER BY an.ID;

这两种方案都是纯SQL实现，比写PL/SQL循环效率高得多，也更易维护~

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