MySQL递归查询

简单递归

输出数字1到10

WITH RECURSIVE number_sequence AS (
  SELECT 1 AS num -- 初始条件
  UNION ALL
  SELECT num + 1 AS num FROM number_sequence WHERE num < 10 -- 递归条件，生成1到10的数字序列
)
SELECT num FROM number_sequence; -- 查询结果

源数据

CREATE TABLE department (
  id int PRIMARY KEY,
  name varchar(20),
  parent_id int
)

insert  into department(id, name, parent_id) values 
(1,'总部',NULL),
(2,'技术部',1),
(3,'开发部',2),
(4,'测试部',2),
(5,'销售部',1),
(6,'市场部',1);

向上递归

向上递归，查询自己的所有祖先

with recursive department_tree as (
select id, name, parent_id, 1 as recursion_level, cast(id as char(20)) as id_path 
from department where id = 4
union all
select d.id, d.name, d.parent_id, 
t.recursion_level+1 as recursion_level, concat(t.id_path, '->', d.id) as id_path 
from department_tree t, department d where t.parent_id = d.id
)
select * from department_tree

向下递归

向下递归，查询自己的所有子集

with recursive department_tree as (
select id, name, parent_id, 1 as recursion_level, cast(id as char(20)) as id_path 
from department where id = 1 
union all
select d.id, d.name, d.parent_id, 
t.recursion_level+1 as recursion_level, concat(t.id_path, '<-', d.id) as id_path 
from department_tree t, department d where d.parent_id = t.id
)
select * from department_tree