MySQL递归查询筛选含匹配节点的树形分支

需求说明 #

在MySQL中，存在具有父子树形结构的表，每个节点包含多个属性。需要提取所有至少有一个节点属性匹配查询条件的完整分支，即从根节点到匹配节点的整条路径上的所有节点。

示例数据 #

------------------------
|id|parent |description |
------------------------
| 1|NULL   |First root  |
| 2|NULL   |Second root |
| 3|1      |First child |
| 4|1      |Second child|
| 5|1      |Third child |
| 6|2      |First child |
| 7|2      |Second child|
| 8|6      |First child |
| 9|6      |Second child|
|10|6      |Third child |
|11|5      |First child |

查询条件 #

WHERE description LIKE "Third%"

期望返回结果 #

------------------------
|id|parent |description |
------------------------
| 1|NULL   |First root  |
| 2|NULL   |Second root |
| 5|1      |Third child |
| 6|2      |First child |
|10|6      |Third child |

现有问题语句 #

原递归查询仅从根节点向下筛选匹配的子节点，无法保留根节点到匹配节点之间的非匹配节点，导致结果不完整：

WITH RECURSIVE tmp (id,parent,description,parent_name, parent_description) AS (
   SELECT id,parent,description,NULL,NULL 
   FROM table 
   WHERE parent IS NULL
UNION ALL
   SELECT table.*, tmp.parent,tmp.description 
   FROM tmp 
   JOIN table ON table.parent=tmp.id 
   WHERE table.description LIKE "Third%" 
)
SELECT * FROM tmp;

可行解决方案 #

正确的思路是从匹配节点向上递归追溯所有祖先节点，确保整条路径的节点都被包含，具体SQL如下：

WITH RECURSIVE matched_paths AS (
    -- 第一步：定位所有符合查询条件的节点
    SELECT id, parent, description
    FROM your_table
    WHERE description LIKE 'Third%'
    UNION ALL
    -- 第二步：递归向上查找每个匹配节点的所有祖先（直到根节点）
    SELECT t.id, t.parent, t.description
    FROM your_table t
    JOIN matched_paths mp ON t.id = mp.parent
)
-- 去重并排序，确保结果结构清晰
SELECT DISTINCT id, parent, description
FROM matched_paths
ORDER BY parent IS NULL DESC, parent, id;

逻辑说明 #

1. 递归CTE的第一部分先找出所有满足条件的节点；
2. 第二部分通过关联父节点，不断向上递归获取每个匹配节点的所有祖先，包括根节点；
3. 使用DISTINCT去重（避免多个子节点共享祖先时重复输出），最后排序让根节点优先展示，结果层级更直观。

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