如何查询task_categories表中符合公开权限规则的树形后代节点

要实现你要求的树形节点筛选——仅保留从公开根节点出发、路径上所有节点均为公开的分支（私有节点的子节点全部排除），可以用**递归公共表表达式（CTE）**来实现，这是处理树形数据最简洁高效的方式。

核心逻辑 #

只遍历从公开根节点开始的分支，一旦遇到is_public = 0的节点，就终止该分支的递归，不再向下查询其子节点；只有父节点已在公开节点集合中，且自身也公开的子节点才会被纳入结果。

实现SQL代码 #

适用于PostgreSQL、MySQL 8.0+、SQL Server等支持递归CTE的数据库：

WITH RECURSIVE public_categories AS (
    -- 锚点：选中公开的根节点（parent_id为null且is_public=1）
    SELECT id, name, parent_id, is_public
    FROM task_categories
    WHERE parent_id IS NULL AND is_public = 1
    
    UNION ALL
    
    -- 递归：仅关联已筛选的公开节点的公开子节点
    SELECT tc.id, tc.name, tc.parent_id, tc.is_public
    FROM task_categories tc
    JOIN public_categories pc ON tc.parent_id = pc.id
    WHERE tc.is_public = 1
)
-- 排除根节点，仅返回后代节点
SELECT id, name, parent_id, is_public
FROM public_categories
WHERE parent_id IS NOT NULL;

执行结果（对应你的示例数据） #

执行后会返回以下符合要求的节点：

补充说明 #

• 如果需要包含根节点的完整树形结构，去掉最后一行WHERE parent_id IS NOT NULL即可。
• 若你的数据库不支持递归CTE（如MySQL 5.x），可以通过应用层迭代查询实现，但效率会低于递归CTE。

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