嵌套集树结构下带终止条件的递归SQL查询需求

没问题，针对你这个嵌套集树的查询需求，我给你整理了一个清晰的解决方案，用递归CTE就能轻松实现！

先明确下需求核心：

• 要保留的节点：所有从根节点到自身的路径上，没有任何祖先节点（包括直接父节点）设置stop_descending标记的节点
• 要排除的节点：如果某个节点的父节点（或更高层祖先）有stop_descending=TRUE，那么该节点及其所有子节点都要被排除
• 递归终止条件：当节点的is_leaf=1时停止递归（不过嵌套集里叶子节点本身没有子节点，递归会自然终止，但我们的逻辑也会适配这个要求）

假设你的表结构是这样的（包含嵌套集必备的lft/rgt字段，以及你的自定义字段）：

CREATE TABLE tree_nodes (
    id INT PRIMARY KEY,
    lft INT NOT NULL,
    rgt INT NOT NULL,
    is_leaf BOOLEAN NOT NULL,
    stop_descending BOOLEAN NOT NULL DEFAULT FALSE
);

解决方案：递归CTE查询 #

下面这个SQL语句会精准筛选出符合要求的节点：

WITH RECURSIVE valid_nodes AS (
    -- 锚点：先找出所有顶级节点（无父节点），且自身没有开启stop_descending
    SELECT 
        id,
        lft,
        rgt,
        is_leaf,
        stop_descending
    FROM tree_nodes
    WHERE NOT EXISTS (
        -- 判定顶级节点：没有任何节点的lft小于它、rgt大于它（即没有父节点）
        SELECT 1 FROM tree_nodes parent
        WHERE parent.lft < tree_nodes.lft AND parent.rgt > tree_nodes.rgt
    )
    AND stop_descending = FALSE

    UNION ALL

    -- 递归：只遍历父节点在有效列表中，且父节点没有stop_descending标记的子节点
    SELECT 
        child.id,
        child.lft,
        child.rgt,
        child.is_leaf,
        child.stop_descending
    FROM tree_nodes child
    JOIN valid_nodes parent 
        ON parent.lft < child.lft AND parent.rgt > child.rgt
    -- 核心条件：父节点不能有stop_descending标记，确保子节点符合要求
    WHERE parent.stop_descending = FALSE
    -- 按照你的要求：当节点是叶子节点时终止递归（叶子节点本身会被保留，只是不再往下遍历）
    AND child.is_leaf = FALSE
)
-- 最后把递归得到的非叶子节点，加上所有符合条件的叶子节点（因为递归里只处理了非叶子）
SELECT id FROM valid_nodes
UNION
SELECT id FROM tree_nodes
WHERE is_leaf = TRUE
AND EXISTS (
    -- 确保叶子节点的父节点在有效列表中，即路径上没有stop标记
    SELECT 1 FROM valid_nodes parent
    WHERE parent.lft < tree_nodes.lft AND parent.rgt > tree_nodes.rgt
);

逻辑解释 #

1. 锚点部分：先锁定所有合法的顶级节点，确保这些根节点本身没有stop_descending标记，否则它们的所有子节点都会被排除。
2. 递归部分：只从合法的父节点往下遍历子节点，一旦父节点有stop_descending标记，就不会继续递归它的子节点，自然排除了整个分支。同时按照你的要求，遇到is_leaf=1的节点就停止递归（因为叶子节点没有子节点，这一步也能避免无意义的遍历）。
3. 最终结果：把递归得到的非叶子节点，加上所有父节点合法的叶子节点，就得到了你要的节点1、2、3，而节点4、5及其子节点会因为父节点的stop_descending标记被彻底排除。

如果你的数据库是MySQL（8.0+支持递归CTE）、PostgreSQL、SQL Server等，这个写法都能直接用，只需要根据你的实际表名和字段名微调即可。

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