PostgreSQL递归CTE过滤问题：排除含非空limit_to的父子节点

基础需求：过滤非空limit_to节点及其后代 #

你的原查询错误在于只检查了节点自身的limit_to是否为空，没有考虑其祖先是否存在非空limit_to。节点2、3虽然自身limit_to为空，但它们的父节点1有非空limit_to，属于需要排除的分支，而原查询的递归CTE初始就包含了这些节点，导致结果错误。

正确思路是：先找出所有需要排除的节点（自身limit_to非空，或属于这类节点的后代），再从全表中排除这些节点。

正确SQL #

WITH recursive excluded AS (
  -- 第一步：找出所有自身limit_to非空的节点
  SELECT id
  FROM categories
  WHERE limit_to IS NOT NULL
  UNION ALL
  -- 第二步：递归找出这些节点的所有后代
  SELECT c.id
  FROM categories c
  JOIN excluded e ON c.parent_id = e.id
)
-- 筛选不在排除列表中的节点
SELECT id, limit_to, parent_id
FROM categories
WHERE id NOT IN (SELECT id FROM excluded)
ORDER BY id;

执行后会返回仅id=4的记录，符合预期。

扩展需求：匹配limit_to IS NULL OR 'tc' = ANY(limit_to)的节点 #

这个需求需要排除**自身limit_to非空且不包含'tc'**的节点及其后代，剩下的就是符合条件的节点（自身符合条件，或在符合条件的节点分支中）。

正确SQL #

WITH recursive excluded AS (
  -- 第一步：找出自身不符合条件的节点（limit_to非空且不含'tc'）
  SELECT id
  FROM categories
  WHERE limit_to IS NOT NULL AND 'tc' <> ALL(limit_to)
  UNION ALL
  -- 第二步：递归找出这些节点的所有后代
  SELECT c.id
  FROM categories c
  JOIN excluded e ON c.parent_id = e.id
)
-- 筛选不在排除列表中的节点
SELECT id, limit_to, parent_id
FROM categories
WHERE id NOT IN (SELECT id FROM excluded)
ORDER BY id;

执行后会返回id=1、2、3、4的记录：

• id=1的limit_to包含'tc'，符合条件
• id=2、3是id=1的后代，被保留
• id=4自身符合条件
• id=5的limit_to不含'tc'，被排除

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