带参数存储过程中使用Recursive CTE批量查询节点对应Flag=Y父节点方案

你原有的单节点递归逻辑只需要做两处调整即可支持指定SetId下的全量节点批量处理：

• 在CTE中新增original_nodeId字段，保留初始查询的节点ID，用于最终结果和起始节点的对应
• 将CTE锚点的单节点筛选条件改为指定SetId的全量节点筛选

以下是MySQL 8.0+ 版本的完整存储过程实现（支持递归CTE的其他数据库仅需调整存储过程的语法壳子，CTE核心逻辑完全通用）：

DELIMITER //
CREATE PROCEDURE GetTargetYNode(IN input_set_id INT)
BEGIN
    WITH RECURSIVE account_node_cte AS (
        -- 锚点：取出指定SetId下的所有节点作为递归起始
        SELECT
            nodeId AS original_nodeId,
            ParentNodeId AS parent_node,
            Flag,
            SetId
        FROM AccountNode
        WHERE SetId = input_set_id
        UNION ALL
        -- 递归：向上查询父节点，当前节点Flag不为Y则继续递归
        SELECT
            cte.original_nodeId,
            a.ParentNodeId AS parent_node,
            a.Flag,
            a.SetId
        FROM AccountNode a
        INNER JOIN account_node_cte cte
            ON a.nodeId = cte.parent_node
        WHERE cte.Flag != 'Y'
    )
    -- 筛选出Flag为Y的最终结果
    SELECT
        original_nodeId AS nodeId,
        parent_node AS parentNode,
        Flag AS flag,
        SetId AS set_id
    FROM account_node_cte
    WHERE Flag = 'Y'
    ORDER BY original_nodeId;
END //
DELIMITER ;

调用方式 #

传入指定的SetId参数即可执行：

CALL GetTargetYNode(1);

针对你提供的样例数据，执行后返回结果和你给出的期望输出完全一致。

补充说明 #

• 你原有代码中锚点部分的(select nodeId , parentNode, flag, set_id) from存在多余的闭合括号，已在上述实现中修正
• 逻辑默认会自动过滤向上递归找不到Flag=Y节点的起始节点，如果需要保留这类节点，可以调整最终查询的过滤条件做兼容
• 如果使用PostgreSQL/SQL Server等其他数据库，仅需要修改存储过程的定义语法，递归CTE部分无需改动

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