MySQL：如何筛选自身或子级含指定文本的parent=0行

需求：从包含id、parent、text1至textN字段的树形结构数据表中，筛选所有parent=0的顶级父行，要求这些父行自身或其任意下属子级行的text字段匹配LIKE '%text4%'。

示例数据 #

预期输出 #

错误尝试的SQL #

SELECT * FROM `table` AS P /*parents*/
INNER JOIN `table` AS C /*childs*/
ON (C.id = P.id) 

WHERE ( 
    
    (
        ((P.`text1` LIKE '%text4%')OR (P.`text2` LIKE '%text4%')OR (P.`text3` LIKE '%text4%')OR (P.`text4` LIKE '%text4%'))
    )
        OR (
            (C.`parent`>0) AND (
                ((C.`text1` LIKE '%text4%')OR (C.`text2` LIKE '%text4%')OR (C.`text3` LIKE '%text4%')OR (C.`text4` LIKE '%text4%'))
            )
                                
        )
    ) 

GROUP BY CASE WHEN C.`parent`>0 THEN C.`parent` ELSE P.`id` END

上述SQL返回的是匹配条件的子级行（id=2、3、5），而非所需的顶级父行。

修正方案 #

方案1：使用递归CTE（适用于MySQL 8+、PostgreSQL、SQL Server等支持递归的数据库） #

递归CTE先定位所有匹配%text4%的节点，再向上追溯它们的所有父节点，最终筛选出其中parent=0的顶级父行并去重：

WITH RECURSIVE matched_nodes AS (
    -- 第一步：找出所有自身text字段包含text4的节点
    SELECT id, parent
    FROM `table`
    WHERE text1 LIKE '%text4%' 
       OR text2 LIKE '%text4%'
       -- 补充text3至textN的匹配条件
       OR textN LIKE '%text4%'
    UNION ALL
    -- 第二步：递归向上查找这些节点的父节点，直到顶级父节点
    SELECT t.id, t.parent
    FROM `table` t
    INNER JOIN matched_nodes mn ON t.id = mn.parent
)
-- 筛选出顶级父行并去重
SELECT DISTINCT t.*
FROM `table` t
INNER JOIN matched_nodes mn ON t.id = mn.id
WHERE t.parent = 0;

方案2：非递归方案（适用于不支持递归CTE的老版本数据库） #

通过关联子查询筛选两类顶级父行：自身含目标文本的，或存在子级（多级子级需逐层扩展子查询）含目标文本的，最后去重：

SELECT DISTINCT p.*
FROM `table` p
WHERE p.parent = 0
  AND (
      -- 父行自身包含text4
      p.text1 LIKE '%text4%' 
      OR p.text2 LIKE '%text4%'
      -- 补充text3至textN的匹配条件
      OR p.textN LIKE '%text4%'
      -- 父行的子级包含text4（多级子级需添加更多嵌套子查询）
      OR EXISTS (
          SELECT 1
          FROM `table` c
          WHERE c.parent = p.id
            AND (c.text1 LIKE '%text4%' 
                 OR c.text2 LIKE '%text4%'
                 -- 补充text3至textN的匹配条件
                 OR c.textN LIKE '%text4%')
          -- 如需支持三级子级，添加以下片段：
          -- OR EXISTS (
          --     SELECT 1 FROM `table` gc WHERE gc.parent = c.id AND ...
          -- )
      )
  );

内容的提问来源于stack exchange，提问作者Dmitry S.