PostgresSQL递归查找树形表中的某段枝叶

PostgresSQL递归查找树形表中的某段枝叶

直接看代码：

--现有一端递归序列串：['房建', 'aaa', ''bbb, '建设用地批准书']，需要查询匹配树形结构内部某一段的所有值
--用到了递归查询
--双向递归，确定递归头和递归尾
WITH RECURSIVE resEnd AS (
    SELECT id, parent_id, level, name, project_id, business_id
    FROM public.technical_folder
    WHERE project_id='1163' AND business_id='1163' AND type=3 AND name = '规划红线内的道路、园林绿化、喷灌设施等竣工图'
    UNION
    SELECT b.id, b.parent_id, b.level, b.name, b.project_id, b.business_id
    FROM resEnd a INNER JOIN public.technical_folder b ON a.parent_id = b.id
),resBegin AS (
    SELECT id, parent_id, level, name, project_id, business_id
    FROM public.technical_folder
    WHERE project_id='1163' AND business_id='1163' AND type=3 AND name = '房建'
    UNION
    SELECT b.id, b.parent_id, b.level, b.name, b.project_id, b.business_id
    -- 从尾部结果集中递归查找，提升查找效率
    FROM resBegin a INNER JOIN resEnd b ON a.id = b.parent_id
)
SELECT a.* from resEnd a INNER JOIN resBegin b ON a.id = b.id ORDER BY a.level;

结果如下：