求助：查询与指定ID存在任意关联关系的所有记录的SQL实现方案

嘿，我来帮你搞定这个SQL问题！你需要的是找出和指定ID（比如这里的1）存在直接或间接关联的所有记录，这其实就是在一个无向图里找整个连通分量——因为你的关联关系是双向的，比如1关联2，那2也关联1，进而关联5，以此类推。

问题分析 #

你之前写的递归CTE有两个核心问题：

• 初始的parent只收集了直接和1关联的RecID1，没覆盖所有关联的ID（包括1本身和关联的RecID2）
• 递归步骤只处理了单向关联（从tree.RecID2找下一个RecID1），没考虑反向的关联路径，导致无法遍历整个关联网络

正确的SQL查询方案 #

下面是能满足你需求的完整代码，我会一步步解释逻辑：

-- 先创建测试表（你已有的初始化代码）
create table testtable (RecID1 int, RecID2 int)
insert into testtable values (1,2)
insert into testtable values (3,1)
insert into testtable values (5,2)
insert into testtable values (8,7)
insert into testtable values (4,5)
insert into testtable values (6,8)
insert into testtable values (1,9);

-- 核心查询：找出与指定ID（这里是1）连通的所有记录
WITH RECURSIVE connected_ids AS (
    -- 初始步骤：获取所有直接和指定ID关联的ID，包括指定ID自己
    SELECT RecID1 AS id FROM testtable WHERE RecID2 = 1
    UNION
    SELECT RecID2 AS id FROM testtable WHERE RecID1 = 1
    UNION
    SELECT 1 AS id  -- 手动加入目标ID本身
    UNION ALL
    -- 递归步骤：双向遍历所有关联的ID，避免重复添加
    SELECT 
        CASE WHEN t.RecID1 = c.id THEN t.RecID2 ELSE t.RecID1 END AS id
    FROM testtable t
    INNER JOIN connected_ids c ON t.RecID1 = c.id OR t.RecID2 = c.id
    WHERE 
        CASE WHEN t.RecID1 = c.id THEN t.RecID2 ELSE t.RecID1 END 
        NOT IN (SELECT id FROM connected_ids)
),
connected_records AS (
    -- 筛选所有至少有一个ID在连通集合中的记录
    SELECT RecID1, RecID2
    FROM testtable
    WHERE RecID1 IN (SELECT id FROM connected_ids) 
       OR RecID2 IN (SELECT id FROM connected_ids)
)
SELECT * FROM connected_records;

代码逻辑拆解 #

1. connected_ids CTE：负责遍历所有和目标ID（1）连通的ID：
   • 初始部分：先抓所有直接和1关联的ID，再加上1本身，确保起点完整
   • 递归部分：双向遍历已找到的ID的关联对象，同时通过NOT IN避免重复添加已经在集合里的ID，防止无限递归
2. connected_records CTE：根据连通ID集合，筛选测试表中所有符合条件的记录——只要记录的RecID1或RecID2属于连通集合，就纳入结果

运行结果 #

当指定ID为1时，返回的结果完全符合你的预期：

RecID1 RecID2
1      2
3      1
5      2
4      5
1      9

如果想让查询更通用，你可以把目标ID改成变量（比如@target_id = 1），这样就能灵活切换要查询的关联起点啦。

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