使用Closure Table模板如何查询数据表中所有无父节点的节点？

前置说明 #

你当前使用的闭包表存储模式中，每个节点存在自引用（节点自身同时是自身的父/子节点，对应闭包表深度为0的记录），根节点的判定逻辑为：排除自引用关系后，不存在其他上级祖先节点的节点。

参考表结构（可适配你实际的业务字段调整） #

• 节点基础表 node：存储节点的核心属性
  • id：节点唯一主键
  • name：节点展示名称
• 闭包关系表 closure：存储所有节点的祖先-后代路径关系
  • ancestor：祖先节点ID，关联node.id
  • descendant：后代节点ID，关联node.id
  • depth：路径深度，自引用关系的深度为0，直接父子关系深度为1，以此类推

根节点查询SQL #

SELECT
  n.id,
  n.name,
  0 AS `level`, -- 根节点层级固定为0，可根据业务规则调整
  JSON_ARRAY() AS `children`, -- 空子女数组，如需填充子节点可结合递归CTE扩展
  NULL AS `parent` -- 根节点无父节点，返回NULL
FROM node n
LEFT JOIN closure c
  ON c.descendant = n.id
  AND c.depth > 0 -- 过滤掉自引用关系
WHERE c.ancestor IS NULL; -- 不存在其他祖先的节点即为根节点

结果示例 #

如果你需要返回带嵌套子节点的完整结构，可以基于上述根节点结果，通过递归CTE关联闭包表遍历所有后代节点，逐层拼接children字段即可。

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