Mysql递归查询

innocent manong

已于 2023-10-26 17:24:07 修改

阅读量67

点赞数 1

文章标签：开发语言 mysql

于 2023-10-26 17:22:16 首次发布

本文链接：https://blog.csdn.net/qq_43718377/article/details/134060905

版权

Mysql递归查询-直观

测试数据

CREATE TABLE `sys_recursion` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL,
  `parent_code` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3


INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(9, '张三爷爷', '0');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(10, '张三', '张三爸爸');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(11, '张三', '张三妈妈');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(12, '张三爸爸', '张三爷爷');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(13, '张三儿子', '张三');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(14, '张三女儿', '张三');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(15, '李四', '李四爸爸');
INSERT INTO sys_recursion
(id, code, parent_code)
VALUES(16, '李四儿子', '李四');

在这里插入图片描述

父节点查询子节点

with recursive cur_recursion as (
select
	code,
	parent_code ,
	0 as level
from
	sys_recursion
where
	code = '张三爸爸'
	-- 这里是起始的父节点ID，您可以根据需要修改
union all
select
	o.code,
	o.parent_code,
	rc.level + 1
from
	sys_recursion o
inner join cur_recursion rc on
	o.parent_code = rc.code )
select
	*
from
	cur_recursion;

在这里插入图片描述

子节点查询父节点

with recursive cur_recursion as (
select
	code,
	parent_code ,
	0 as level
from
	sys_recursion
where
	code = '张三儿子'
union all
select
	o.code,
	o.parent_code,
	rc.level + 1
from
	sys_recursion o
inner join cur_recursion rc on
	o.code = rc.parent_code )
select
	*
from
	cur_recursion;