MySQL 8双父节点递归查询：仅保留双亲均在结果中的节点

我需要处理一个双父节点的层级结构场景，目标是筛选出仅双亲都在结果集中的节点。

最初我设想的查询逻辑如下（注：原代码存在笔误，重复写了parent2），但MySQL不允许在递归CTE的子查询中多次引用自身，导致无法执行：

WITH RECURSIVE granters (id, parents) AS (
    SELECT id FROM `hierarchy` WHERE id in ("id1", "id2", "id6"....)
    UNION ALL
    SELECT h1.id, h1.parents FROM `hierarchy` h1 
     WHERE h1.parent1 in (SELECT id FROM granters)
     AND h1.parent2 in (SELECT id FROM granters)
)
SELECT * FROM granters g

我尝试了一个替代方案，但存在逻辑漏洞：

WITH RECURSIVE granters (id) AS (
    SELECT id FROM `hierarchy` WHERE id in ("A", "B")
    UNION ALL
    SELECT h1.id FROM `hierarchy` h1 INNER JOIN granters g 
        ON h1.parent1 = g.id
        OR h1.parent2 = g.id
)
SELECT * FROM hierarchy g 
WHERE g.id IN ("A", "B")
OR (
   g.parent1 IN (SELECT * FROM granters)
   AND g.parent2 IN (SELECT * FROM granters)
)

这个方案会先收集所有至少有一个父节点在结果中的元素，再过滤双亲不全在的节点。但如果某个节点的双亲都在这个宽松收集的集合里，其中一个双亲本身不符合条件（比如D的父H不在结果集），最终还是会错误返回该节点（比如E）。

测试数据 #

问题表现 #

实际输出错误包含了E，而期望输出仅包含A、B、C。

正确的递归查询方案 #

核心思路是在递归过程中只添加双亲均已在结果集的节点，从根源避免引入不符合要求的中间节点：

WITH RECURSIVE valid_nodes AS (
    -- 初始选定的基础节点
    SELECT id, parent1, parent2
    FROM `hierarchy`
    WHERE id IN ('A', 'B')
    
    UNION ALL
    
    -- 递归筛选：仅双亲都在valid_nodes中的节点才会被加入
    SELECT h.id, h.parent1, h.parent2
    FROM `hierarchy` h
    JOIN valid_nodes v1 ON h.parent1 = v1.id
    JOIN valid_nodes v2 ON h.parent2 = v2.id
)
SELECT id, parent1, parent2
FROM valid_nodes;

方案说明 #

1. 初始步骤：先将选定的基础节点（A、B）加入结果集。
2. 递归步骤：通过两次内连接valid_nodes，确保当前节点的parent1和parent2都已在结果集中，才会被纳入。这样D因parent2=H不在结果集，永远不会被加入；E因parent2=D不在结果集，自然也不会被选中。
3. 最终结果完全匹配期望输出：

测试数据建表语句 #

CREATE TABLE `hierarchy` (
  `id` char(36) NOT NULL,
  `parent1` char(36),
  `parent2` char(36),
  PRIMARY KEY (`id`)
);

INSERT INTO `hierarchy` (id,parent1,parent2) VALUES
     ('A','A1','A2'),
     ('B','B1','B2'),
     ('C','A','B'),
     ('D','B','H'),
     ('E','C','D');

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