递归 CTE（WITH RECURSIVE）

Oracle

-- Hierarchy with CONNECT BY:
SELECT id, name, manager_id, LEVEL
FROM employee
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- Alternative (11gR2+): standard recursive WITH (the RECURSIVE keyword is not written)
WITH tree (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree;

用于遍历层次（树形）数据：组织结构图、物料清单等。Oracle 的传统方式是 CONNECT BY / PRIOR 和 LEVEL 伪列；START WITH 设置根行。自 11gR2 起也支持标准的递归 CTE。

PostgreSQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

WITH RECURSIVE 用 UNION ALL 将锚点查询和递归部分组合起来；递归部分反复执行，直到结果为空。非常适合层次结构和图遍历。使用 UNION（而非 ALL）或循环保护防止无限循环。

MySQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

MySQL 8.0（以及 MariaDB 10.2+）支持符合标准的 WITH RECURSIVE 语法。递归深度受系统变量 cte_max_recursion_depth 限制（默认 1000）。旧版本需要在存储过程中编写循环。

SQL Server

WITH tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level
OPTION (MAXRECURSION 100);

在 SQL Server 中，递归 CTE 不写 RECURSIVE 关键字；包含 UNION ALL 且引用自身的 CTE 会被自动视为递归。默认递归上限为 100；可用 OPTION (MAXRECURSION n) 修改（0 = 不限制，谨慎使用）。

MongoDB

// Closest equivalent: the $graphLookup stage
db.employee.aggregate([
  { $match: { manager_id: null } },
  { $graphLookup: {
      from: "employee",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "manager_id",
      as: "subordinates"
  } }
])

MongoDB 没有递归 CTE；层次遍历使用 $graphLookup 聚合阶段。connectFromField/connectToField 设置关联字段，maxDepth 设置深度上限。可在单个查询中解析树和图关系。

Redis

# Not applicable: Redis has no recursive queries.

Redis 不提供查询语言。层次/图遍历在应用代码中或通过服务器端 Lua 脚本完成；在核心 Redis 中，树遍历需在客户端编写。（过去使用的 RedisGraph 模块已于 2023 年停止支持。）

Elasticsearch

# Not applicable: recursive queries are not supported.
# Approach: model with parent-child (join field) or nested,
# deep hierarchies are traversed on the application side.

Elasticsearch 不提供递归查询（没有 WITH RECURSIVE / CONNECT BY 的等价物）。层次关系通过 'join'（parent/child）字段类型建模，或将树路径（materialized path）存储在字段中；多层遍历通常在应用中完成。