SQL实现父子关联表查询指定ID所有直接间接关联节点

原有代码问题 #

• 固定写死查询条件为child_id = 6，未支持输入ID动态传入
• 递归逻辑没有去重机制，存在循环关联数据时会触发递归溢出
• 最终返回结果为关联关系对，没有提取所有独立关联ID，也未做去重处理

实现代码 #

以下为兼容MySQL 8.0+/PostgreSQL/Oracle等支持递归CTE数据库的通用实现：

-- 替换 :INPUT_ID 为你需要查询的输入ID，也可改为存储过程入参
WITH RECURSIVE all_related_ids AS (
    -- 初始节点：输入ID本身
    SELECT :INPUT_ID AS id
    UNION
    -- 向上递归：查询当前关联ID对应的所有父级
    SELECT rt.parent_id AS id
    FROM all_related_ids ari
    JOIN ARTAPAY_TEMP.REL_TEST rt ON ari.id = rt.child_id
    UNION
    -- 向下递归：查询当前关联ID对应的所有子级
    SELECT rt.child_id AS id
    FROM all_related_ids ari
    JOIN ARTAPAY_TEMP.REL_TEST rt ON ari.id = rt.parent_id
)
-- 直接查询所有关联ID列表
SELECT id FROM all_related_ids;

如果需要输出为逗号分隔的字符串，根据你使用的数据库修改最后一句查询即可：

• MySQL：SELECT GROUP_CONCAT(DISTINCT id) AS related_ids FROM all_related_ids;
• PostgreSQL：SELECT STRING_AGG(DISTINCT id::TEXT, ',') AS related_ids FROM all_related_ids;
• Oracle：SELECT LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS related_ids FROM all_related_ids;

逻辑说明 #

使用UNION而非UNION ALL进行递归合并，自动对查询到的ID去重，同时避免关联关系存在环时的无限递归问题。输入ID=9执行时，返回结果和需求示例完全匹配。

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