如何用SQL查询ID间的直接与间接关联关系？

你要解决的核心是找出数据中的连通分量——也就是所有通过直接或间接关联连在一起的ID集合，然后生成集合内的所有两两ID对。下面是针对你的数据的具体实现，同时处理NULL、自关联、双向记录这些特殊情况：

1. 预处理原始数据 #

先过滤掉带NULL的记录（NULL无法参与关联），也可以选择性过滤自关联记录（ID_1=ID_2），这类记录对关联关系没有实质贡献：

WITH clean_data AS (
    SELECT 
        ID_1, 
        ID_2
    FROM your_table_name
    WHERE 
        ID_1 IS NOT NULL 
        AND ID_2 IS NOT NULL
        AND ID_1 != ID_2 -- 可选：移除自关联记录
),

2. 用递归CTE遍历所有关联ID #

递归公共表表达式（CTE）是处理这类关联遍历的常用方法，我们用它找出所有直接/间接关联的ID，并用每个连通分量里最小的ID作为标识，方便后续分组：

recursive_relations AS (
    -- 初始锚点：从清洗后的数据开始，每个ID作为关联起点
    SELECT 
        ID_1 AS source_id, 
        ID_2 AS connected_id,
        LEAST(ID_1, ID_2) AS component_root -- 用分量内最小ID作为根节点，统一分组标识
    FROM clean_data
    UNION ALL
    -- 递归遍历：继续查找当前ID关联的其他ID
    SELECT 
        r.source_id, 
        c.ID_2 AS connected_id,
        r.component_root
    FROM recursive_relations r
    JOIN clean_data c ON r.connected_id = c.ID_1
    WHERE c.ID_2 NOT IN (SELECT connected_id FROM recursive_relations WHERE source_id = r.source_id) -- 避免循环遍历
)

3. 生成所有关联ID对 #

从递归结果中提取同一连通分量的所有ID，生成两两组合，同时去重（避免重复出现(0002,0003)和(0003,0002)，如果需要保留双向记录可去掉相关处理）：

SELECT DISTINCT
    LEAST(a.connected_id, b.connected_id) AS ID_A,
    GREATEST(a.connected_id, b.connected_id) AS ID_B
FROM recursive_relations a
JOIN recursive_relations b 
    ON a.component_root = b.component_root
    AND a.connected_id != b.connected_id
ORDER BY ID_A, ID_B;

额外说明 #

• 如果需要保留双向记录（比如同时显示(0002,0003)和(0003,0002)），可以删除LEAST/GREATEST和DISTINCT，直接查询a.connected_id AS ID_A, b.connected_id AS ID_B，只需保证a.connected_id != b.connected_id即可。
• 不同SQL方言对递归的优化方式有差异，比如PostgreSQL支持CYCLE子句更高效地防止循环，SQL Server可通过MAXRECURSION限制递归次数。
• 针对你的示例数据，最终会输出所有同组的ID对：(0001,0002)、(0001,0003)、(0001,0004)、(0002,0003)、(0002,0004)、(0003,0004)。

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