如何利用SQL子查询实现父子级目录结构的层级查询？-SQL-PHP中文网

递归cte可查询无限层级目录结构，需表中含id和parent_id字段且根节点parent_id为null；postgresql、sql server、sqlite 3.8.3+和mysql 8.0+支持，旧版mysql需应用层模拟。

如何利用sql子查询实现父子级目录结构的层级查询？

用 `WITH RECURSIVE` 查询无限层级目录结构

PostgreSQL、SQL Server、SQLite 3.8.3+ 和 MySQL 8.0+ 支持递归 CTE，这是查父子目录最直接的方式。不支持的数据库（如旧版 MySQL）必须用应用层拼接或多次查询模拟。

关键点在于：父节点和子节点必须在同张表里（比如 categories 表含 id 和 parent_id 字段），且根节点的 parent_id 为 NULL 或 0。

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE parent_id IS NULL  -- 根节点
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.level + 1
  FROM categories c
  INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, id;

level 字段可用来缩进显示或限制深度（加 WHERE level ）
MySQL 8.0+ 默认递归深度限制为 1000，超限会报错 ERROR 3636，需调大 cte_max_recursion_depth
PostgreSQL 中若出现循环引用（A→B→A），会报错 infinite recursion detected，建议加 cycle 子句（但不是所有数据库都支持）

MySQL 5.7 及更早版本只能靠多次 JOIN 模拟有限层级

没有递归 CTE 时，最多能查固定几级，比如三级目录就写三表 JOIN，四级就得四次 JOIN —— 不灵活，且容易漏数据或笛卡尔积爆炸。

典型写法是自连接，每层对应一次 LEFT JOIN：

SELECT 
  t1.name AS level1,
  t2.name AS level2,
  t3.name AS level3
FROM categories t1
LEFT JOIN categories t2 ON t2.parent_id = t1.id
LEFT JOIN categories t3 ON t3.parent_id = t2.id
WHERE t1.parent_id IS NULL;

只适用于层级明确且稳定（比如“省-市-区”固定三级）
LEFT JOIN 是为了保留中间某层为空的情况（如一级目录下无二级）
性能随 JOIN 数量指数下降，超过 4 层基本不可用；索引必须建在 parent_id 上，否则全表扫描
无法返回统一字段结构（比如每行都是 id/name/level），前端解析麻烦

子查询嵌套写法只适合单路径追溯（比如查某个目录的全部祖先）

如果目标只是“给定一个 id，找出它所有上级目录”，用多层子查询比递归更兼容，也更易理解。

Creatie

Creatie是一款曾面向 UI/UX 设计流程的 AI 辅助设计工具。

下载

例如查 id = 100 的完整路径（从根到它自己）：

SELECT * FROM categories
WHERE id IN (
  SELECT parent_id FROM categories WHERE id = 100
  UNION ALL
  SELECT parent_id FROM categories WHERE id IN (
    SELECT parent_id FROM categories WHERE id = 100
  )
  UNION ALL
  SELECT parent_id FROM categories WHERE id IN (
    SELECT parent_id FROM categories WHERE id IN (
      SELECT parent_id FROM categories WHERE id = 100
    )
  )
);

这种写法本质是手动展开递归，层数完全由 SQL 长度决定，维护成本高
不能保证顺序（谁是第一级谁是第二级），需要额外用 ORDER BY 或应用层排序
比起 WITH RECURSIVE，它无法自然表达“向下找子节点”，只适合向上溯源
Oracle 用户可能习惯用 CONNECT BY PRIOR，但那是方言，跨库不可移植

应用层组装仍是很多团队的实际选择

当数据库不支持递归、层级动态变化、或需配合权限过滤时，一次性查出全部目录再用代码组装树结构，反而更可控。

核心思路：一次 SELECT * 拿全量，然后按 parent_id 建哈希映射，遍历构建树：

// 伪代码示例（Python）
rows = db.execute("SELECT id, name, parent_id FROM categories")
nodes = {r['id']: {'id': r['id'], 'name': r['name'], 'children': []} for r in rows}
roots = []
for r in rows:
    if r['parent_id'] is None:
        roots.append(nodes[r['id']])
    else:
        nodes[r['parent_id']]['children'].append(nodes[r['id']])