使用Closure Table闭包表存储层级数据时如何仅获取一级子节点

核心思路 #

你使用的标准Closure Table结构存储了所有祖先-后代的关联关系（包含节点自身关联），要筛选直属子节点，本质就是排除所有存在中间层级的后代节点，仅保留和目标父节点直接相邻的子节点。

具体SQL实现 #

以查询id=1（Electronics）的直属子节点为例，SQL语句如下：

SELECT cn.id, cn.name
FROM tree_path tp
INNER JOIN category_name cn ON tp.children = cn.id
-- 筛选父节点为目标查询节点
WHERE tp.parent = 1
-- 排除节点自身的关联记录
AND tp.children != tp.parent
-- 排除存在中间父节点的后代节点
AND NOT EXISTS (
    SELECT 1
    FROM tree_path tp_mid
    WHERE tp_mid.parent = tp.parent
      AND tp_mid.children != tp.children
      AND tp_mid.children != tp.parent
      AND EXISTS (
          SELECT 1
          FROM tree_path tp_sub
          WHERE tp_sub.parent = tp_mid.children
            AND tp_sub.children = tp.children
      )
);

内层嵌套的NOT EXISTS逻辑用于过滤存在中间层级的后代：如果当前查询的子节点和目标父节点之间还存在其他中间节点，就会被排除，仅保留直接相邻的直属子节点。

查询结果说明 #

针对你提供的测试数据，执行上述查询得到的id=1直属子节点如下：

• id: 2，名称：Computers
• id: 3，名称：Accessories & Supplies
• id: 4，名称：Camera & Photo
  和你示例图中“从C节点出发仅返回直属后代G、H、F”的需求逻辑完全匹配，不会返回更深层级的孙节点、重孙节点。

优化建议 #

如果需要高频查询直属子节点，可以在tree_path表中新增depth字段，记录每个祖先-后代关联的层级差（自身关联depth=0，直属父子depth=1，隔代depth=2，以此类推），之后查询直属子节点可以直接简化为：

SELECT cn.id, cn.name
FROM tree_path tp
INNER JOIN category_name cn ON tp.children = cn.id
WHERE tp.parent = 1 AND tp.depth = 1;

查询效率会提升数倍，更适合高并发场景。

内容的提问来源于stack exchange，提问作者Michael Joseph Jackson