递归SQL查询问题：筛选is_public为0且所有祖先均为0的分类

问题所在 #

你的SQL逻辑方向完全搞反了：

• 当前递归是从所有is_public=0的节点出发，向下遍历子节点，初始查询直接把所有自身is_public=0的节点纳入CTE，根本没检查这些节点的祖先是否满足is_public=0的条件。
• 举个例子：如果某个节点自身is_public=0，但它的父节点is_public=1，这个节点会被初始查询直接选中，最终出现在结果里，这就违反了「所有祖先节点is_public也为0」的要求。

正确的SQL写法 #

应该递归向上追溯每个节点的所有祖先，验证包括自身在内的所有节点是否都满足is_public=0：

WITH RECURSIVE node_ancestors AS (
    -- 初始步骤：取出所有节点，标记自身是否满足is_public=0
    SELECT 
        id, 
        name, 
        is_public,
        (is_public = 0) AS all_ancestors_valid
    FROM task_categories

    UNION ALL

    -- 递归向上找父节点，更新标记：只有当前标记为真，且父节点is_public也为0，才保持有效
    SELECT 
        na.id, 
        na.name, 
        na.is_public,
        na.all_ancestors_valid AND (tc.is_public = 0)
    FROM node_ancestors na
    JOIN task_categories tc ON na.parent_id = tc.id
)
-- 筛选出所有祖先（含自身）都符合条件的节点，去重（每个节点会有多条递归记录）
SELECT DISTINCT id, name, is_public
FROM node_ancestors
WHERE all_ancestors_valid = true;

逻辑说明 #

1. 初始查询取出所有节点，先标记自身是否满足is_public=0；
2. 递归阶段不断向上找父节点，逐步验证祖先的is_public状态：只要有一个祖先is_public=1，标记就会变为false；
3. 最后筛选出标记为true的节点，就是满足「自身is_public=0且所有祖先节点is_public也为0」的记录。

比如当ID=1的is_public设为1时，所有子节点的递归检查都会发现祖先ID=1不符合条件，最终不会返回任何记录，完全符合需求。

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