如何将SQL递归查询作为AND条件筛选符合要求的父级请求ID

现有请求层级结构规则如下：

• 顶层请求为所有下级请求的根父节点
• 所有请求都包含id、parent_id、state三个字段
  对应流程图：
  

需求说明 #

需要筛选出满足所有AND条件的父级ID，其中最后一个校验条件需要用到递归查询，不清楚如何将已完成的递归查询作为AND条件加入主筛选逻辑。

现有递归查询代码 #

已实现的递归查询功能符合预期，可校验指定ID的子树是否存在not-legit状态，代码如下：

with cte 
    as (select id, state
        from tbl_request as rH 
        WHERE id = /* each id from the very first select */
        UNION ALL
        select rH.id, rH.state
        from tbl_request as rH 
        join cte
          on rH.parent_id = cte.id
             and (cte.state is null or cte.state NOT IN('not-legit'))
       )
    select case when exists(select 1 from cte where  cte.state IN('not-legit'))
        then 1 else 0 end

实现思路 #

1. 给递归CTE新增root_id字段，标记每个节点归属的根父级ID，一次性完成所有待校验父级的子树遍历
2. 按root_id聚合得到每个父级的校验结果（子树是否存在not-legit状态）
3. 将校验结果集和初始筛选查询做关联，即可等效为新增了一个AND递归校验条件

完整代码示例 #

SELECT DISTINCT r.root_id
FROM (
    -- 第一层：放置你原本的所有筛选条件，得到待校验的父级ID集合
    SELECT id AS root_id
    FROM tbl_request
    WHERE 
        parent_id IS NULL -- 示例条件：仅筛选根节点，可替换为你的其他业务条件
        -- 此处添加你所有已有的AND筛选条件
) r
INNER JOIN (
    -- 改造后的递归校验CTE
    WITH RECURSIVE cte AS (
        -- 锚点：取出所有请求节点作为遍历起点
        SELECT id AS root_id, id, state
        FROM tbl_request
        UNION ALL
        -- 递归遍历子节点
        SELECT cte.root_id, rH.id, rH.state
        FROM tbl_request rH
        INNER JOIN cte ON rH.parent_id = cte.id
            AND (cte.state IS NULL OR cte.state NOT IN('not-legit'))
    )
    -- 聚合得到校验通过的父级ID
    SELECT root_id
    FROM cte
    GROUP BY root_id
    -- 校验规则：子树中不存在not-legit状态，和你原有逻辑一致
    HAVING MAX(CASE WHEN state IN('not-legit') THEN 1 ELSE 0 END) = 0
) v
ON r.root_id = v.root_id

逻辑说明 #

• 你只需要在第一层的r子查询中添加你所有的非递归筛选条件，关联后的结果就自动满足「所有原有条件 + 递归校验条件」的AND逻辑
• 若你需要判断的是子树存在not-legit状态才符合要求，把HAVING后的=0改成=1即可

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