基于Nodes和Links表，如何从单个节点ID获取完整关联节点网络？

你的问题核心在于原来的递归查询只做了单向链式遍历（只沿着id_from → id_to的方向走），完全没处理反向的关联（比如节点3被节点4指向的情况），所以才会漏掉集群里的部分节点和链路。

结合你的业务场景——设备集群识别（无环连通图），我们可以调整递归逻辑，改成双向遍历所有关联节点，确保把整个连通分量里的所有节点和链路都捞出来。

修正后的查询方案 #

先看针对你样本数据的完整SQL：

WITH RECURSIVE links AS (
 SELECT 1 AS id_from, 2 AS id_to
 UNION
 SELECT 2 AS id_from, 3 AS id_to
 UNION
 SELECT 4 AS id_from, 3 AS id_to
),
-- 第一步：收集集群内所有节点
connected_nodes AS (
 -- 初始节点：从目标ID（这里是1）开始
 SELECT 1 AS node_id
 UNION ALL
 -- 递归遍历：找到所有与已收集节点有任意关联的未访问节点
 SELECT 
   CASE 
     WHEN l.id_from = cn.node_id THEN l.id_to 
     ELSE l.id_from 
   END AS node_id
 FROM connected_nodes cn
 JOIN links l ON cn.node_id IN (l.id_from, l.id_to)
 -- 避免重复访问，防止潜在的循环（虽然你说集群无环，但加这个更安全）
 WHERE CASE 
         WHEN l.id_from = cn.node_id THEN l.id_to 
         ELSE l.id_from 
       END NOT IN (SELECT node_id FROM connected_nodes)
),
-- 第二步（可选）：获取集群内的所有关联链路
full_network_links AS (
 SELECT l.id_from, l.id_to
 FROM links l
 WHERE l.id_from IN (SELECT node_id FROM connected_nodes)
   AND l.id_to IN (SELECT node_id FROM connected_nodes)
)
-- 根据需求选择输出：要么所有节点，要么所有链路
-- SELECT * FROM connected_nodes;
SELECT * FROM full_network_links;

查询结果说明 #

执行后，full_network_links会返回所有集群内的关联：

id_from | id_to
--------|------
1       | 2
2       | 3
4       | 3

connected_nodes则会返回集群里的所有设备ID：1,2,3,4，完全满足你“通过任意设备ID获取整个集群”的需求。

逻辑解释 #

1. connected_nodes CTE：
   • 从目标节点（比如1）出发，先把它加入集合。
   • 递归阶段，不再只看id_from等于上一个节点的情况，而是检查当前集合里的节点是否是任意一条链路的id_from或id_to，找到对应的关联节点，并且只加入未访问过的节点。这样不管链路方向如何，所有关联节点都会被纳入。
2. full_network_links CTE：
   • 基于已经找到的所有集群节点，筛选出所有属于这个集群的链路，得到完整的关联网络。

适配你的业务场景 #

因为你明确禁止同一集群内重复关联，所以你的图是无环连通图，这个查询不会出现无限递归的问题，而且效率也不错——每次递归只处理未访问的节点，不会重复遍历。

如果要支持“任意节点作为入口”，只需要把初始的SELECT 1 AS node_id改成SELECT :target_node_id AS node_id（:target_node_id是你传入的设备ID参数）即可。

内容的提问来源于stack exchange，提问作者Pådne