Oracle双向层级查询获取与指定用户关联的所有记录的实现问题

嗨，我明白你遇到的问题了——你原来的层级查询只沿着friend_from到friend_to的单向路径遍历，所以只能拿到BOB和JOHAN的那条记录，没法捕捉到TRACY和JOHAN这种反向的关联关系对吧？咱们来调整一下查询，让它能双向遍历所有关联的记录。

首先来说说你原查询的问题：CONNECT BY PRIOR friend_from = friend_to这个条件只规定了“上一条记录的friend_from等于当前记录的friend_to”这一种遍历方向，相当于只跟着“谁是谁的朋友”的正向关系走，但JOHAN和TRACY的关系是TRACY作为friend_from指向JOHAN，这个方向和你设定的遍历方向相反，自然就被漏掉了。

那怎么实现双向查询呢？我们需要让层级查询同时支持两种遍历方向，还要避免循环重复，给你两个可行的方案：

方案一：使用CONNECT BY（传统层级查询写法） #

SELECT DISTINCT friend_from, friend_to
FROM friends
START WITH friend_from = 'BOB' OR friend_to = 'BOB'
CONNECT BY NOCYCLE 
    (PRIOR friend_from = friend_to OR PRIOR friend_to = friend_from)

我来拆解一下这个查询的关键点：

• START WITH里加上friend_to = 'BOB'：虽然你的例子里没有，但通用场景下如果有其他用户直接关联到BOB的记录，也能被纳入查询起点
• CONNECT BY NOCYCLE：NOCYCLE关键字是为了防止出现循环关联（比如A是B的朋友，B也是A的朋友）导致的无限遍历问题
• 连接条件(PRIOR friend_from = friend_to OR PRIOR friend_to = friend_from)：同时支持两种遍历方向——要么顺着friend_from→friend_to走，要么反过来从friend_to→friend_from找关联记录
• DISTINCT：因为双向遍历可能会重复取出同一条记录，用它来做去重处理

把这个查询用到你的数据上，就能得到你想要的两条记录啦。

方案二：使用递归CTE（Oracle 11gR2及以上版本支持） #

如果你更习惯用递归公共表表达式（CTE）的写法，也可以试试这个：

WITH recursive_friends AS (
    -- 初始数据集：所有直接关联BOB的记录
    SELECT friend_from, friend_to
    FROM friends
    WHERE friend_from = 'BOB' OR friend_to = 'BOB'
    UNION ALL
    -- 递归遍历：找到所有和已获取记录相关的新记录
    SELECT f.friend_from, f.friend_to
    FROM friends f
    JOIN recursive_friends rf 
        ON f.friend_from = rf.friend_to OR f.friend_to = rf.friend_from
    -- 避免重复添加已存在的记录
    WHERE NOT EXISTS (
        SELECT 1 
        FROM recursive_friends rf2 
        WHERE rf2.friend_from = f.friend_from AND rf2.friend_to = f.friend_to
    )
)
SELECT * FROM recursive_friends;

这个写法逻辑更直观，先拿到所有直接和BOB相关的记录，再递归找出所有和这些记录有关联的新记录，直到没有新记录为止。

备注：内容来源于stack exchange，提问作者JGND070