首先创建一个熟悉的机构表

机构表定义
插入几条测试数据:

测试数据
查询所有下级机构
-- deep是临时表
with recursive deep(id,name,parent) as (
-- 初始化 执行一次
select id,name,parent from organization where id = 6
union all
-- 接下来不断的执行下面的语句来递归
select o.id,o.name,o.parent from organization o
join deep on o.parent = deep.id
)
select * from deep;
union all上面的是初始化语句,只会执行一次,查到了开发部这一行记录。
接下来下面的join会用初始化的语句去原来的organization表去join获取所有开发部的子部门,然后再用这些子部门去join更下面的部门。
执行的结果如下:

下级机构查询结果
查询所有上级机构
如下想查询开发部的所有上级部门的话上面的递归查询语句简单改一下就可以了:
-- deep是临时表
with recursive deep(id,name,parent) as (
-- 初始化 执行一次
select id,name,parent from organization where id = 10
union all
-- 接下来不断的执行下面的语句来递归
select o.id,o.name,o.parent from organization o
join deep on o.id = deep.parent
)
select * from deep;
执行结果如下:

上级部门查询结果
注意 union all下面的递归查询不能用聚合函数或者窗口函数不然会报错
Recursive Common Table Expression 'temp' can contain neither
aggregation nor window functions in recursive query block
mysql
mysql对递归的深度是有限制的,默认的递归深度是1000。
可以通过 show variables like 'cte_max_recursion_depth';进行查看

递归次数
也可以通过select语句最大执行时间对递归加以显示,show variables lile 'max_execution_time';

最大执行时间