问题

假设有一张表

（mysql 8.0以上）
CREATE TABLE `test_0216` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

或者用pg库（postgresql）
CREATE TABLE "test"."test_0216" (
  "id" int4 NOT NULL,
  "pid" int4,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  CONSTRAINT "test_0216_pkey" PRIMARY KEY ("id")
);

该表用于存放树状目录的数据
现在有如下数据
1   null    A
2   1   B
3   1   C
4   2   B1
5   4   B11
6   4   B12
怎么写一条sql，查询B目录下，所有的子目录名（B1，B11,B12）

解决

查询sql如下

WITH RECURSIVE cte AS (
    SELECT id, pid, name
    FROM test_0216
    WHERE id = 2  -- B节点的id
    UNION ALL
    SELECT t.id, t.pid, t.name
    FROM test_0216 t
    INNER JOIN cte ON t.pid = cte.id
)
SELECT name 
FROM cte 
WHERE id != 2;  -- 排除B节点自身

什么是递归公共表表达式（CTE）

MySQL 的递归公用表表达式（Recursive Common Table Expression，简称递归CTE） 是一种通过自我引用的方式处理树状或层次化数据的查询技术。它在 MySQL 8.0 及以上版本中支持，能够简化递归查询逻辑（例如查询目录树、组织结构、评论的嵌套回复等）。

什么是递归CTE？

基本结构：
递归CTE由两部分组成：
锚成员（Anchor Member）：初始查询，定义递归的起点。
递归成员（Recursive Member）：基于前一次迭代的结果进行查询，直到终止条件满足。
两部分通过 UNION ALL 连接。
执行流程：
第一步：执行锚成员，生成初始结果集。
第二步：将前一步的结果作为输入，执行递归成员，生成新的结果。
重复第二步，直到递归成员返回空结果。
最终将所有结果合并。

示例模板：

WITH RECURSIVE cte_name AS (
   -- 锚成员（初始查询）
   SELECT ... FROM table WHERE initial_condition
   UNION ALL
   -- 递归成员（引用自身）
   SELECT ... FROM table JOIN cte_name ON recursive_condition
)
SELECT ... FROM cte_name;

为什么使用递归CTE？

（查询B节点下的所有子目录），递归CTE是最简洁且高效的解决方案。以下是设计思路：

1. 锚成员：定位起点

SELECT id, pid, name FROM test_0216 WHERE id = 2

目的：找到B节点的信息（假设B的id=2）。
作用：作为递归的起点，后续步骤基于此展开。

2. 递归成员：逐层向下查找子节点

SELECT t.id, t.pid, t.name 
FROM test_0216 t
INNER JOIN cte ON t.pid = cte.id

逻辑：每次递归时，查找父节点为当前结果集中节点的子节点。
终止条件：当某次递归不再产生新数据（即没有子节点）时，递归结束。

3. 最终查询：排除父节点自身

SELECT name FROM cte WHERE id != 2;

目的：过滤掉初始的B节点，只保留其子节点名称。

递归CTE的核心优势

简洁性：
无需修改表结构（如添加path字段）。
无需编写存储过程或复杂循环逻辑。
动态处理层级：
无论树的深度如何，递归CTE自动遍历所有子节点。
示例中B的层级是3层（B → B1 → B11/B12），但递归CTE无需预先知道层级深度。
标准化支持：
递归CTE是SQL标准的一部分（如SQL:1999），在支持它的数据库（如MySQL 8.0+、PostgreSQL）中通用。

递归CTE的注意事项

终止条件必须存在：
如果递归成员无法终止（如循环引用），查询会陷入死循环。
MySQL默认设置递归深度限制（通过cte_max_recursion_depth变量，默认1000层），超限会报错。
性能优化：
对pid字段添加索引可显著提升递归查询速度。
避免在递归成员中使用复杂计算或大表连接。
版本限制：
MySQL 5.7及以下版本不支持递归CTE，需使用替代方案（如存储过程或路径枚举）。

与其他方法的对比

总结

递归CTE是处理树状数据的理想工具，但在MySQL 5.7等旧版本中不可用。其核心价值在于：

通过自我引用简化递归逻辑。
自动处理任意深度的层级结构。
保持查询的标准化和可读性。

如果无法升级到MySQL 8.0，路径字段或存储过程是更实际的替代方案。

递归公用表表达式(CTE)应用场景

问题假设有一张表（mysql 8.0以上） CREATE TABLE `test_0216` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) DEFAULT NULL, `name` varchar(255) DEFAUL

问题

解决