文档章节

mysql递归问题

 阿狼仔
发布于 2016/12/10 16:59
字数 717
阅读 11
收藏 0

有个小坑:

    在创建function时 , 名字写错了, 这里应该是getPartList, 导致下面的sql运行不了, 这个小问题不注意还纠结了一下怎么回事。

---下面是原文:转至 云栖社区

最近遇到了一个问题,在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询?
在Oracle中我们知道有一个Hierarchical Queries可以通过CONNECT BY来查询,但是,在MySQL中还没有对应的函数!!!

下面给出一个function来完成的方法

下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。

创建表treenodes(可以根据需要进行更改)

1
2
3
4
5
6
7
8
9
10
-- ---------------------------- 
-- Table structure for `treenodes` 
-- ---------------------------- 
DROP TABLE IF EXISTS `treenodes`; 
CREATE TABLE `treenodes` ( 
  `id` int(11) NOT NULL, 
  `nodename` varchar(20) DEFAULT NULL, 
  `pid` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

插入几条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- ---------------------------- 
-- Records of treenodes 
-- ---------------------------- 
INSERT INTO `treenodes` VALUES ('1', 'A', '0'); 
INSERT INTO `treenodes` VALUES ('2', 'B', '1'); 
INSERT INTO `treenodes` VALUES ('3', 'C', '1'); 
INSERT INTO `treenodes` VALUES ('4', 'D', '2'); 
INSERT INTO `treenodes` VALUES ('5', 'E', '2'); 
INSERT INTO `treenodes` VALUES ('6', 'F', '3'); 
INSERT INTO `treenodes` VALUES ('7', 'G', '6'); 
INSERT INTO `treenodes` VALUES ('8', 'H', '0'); 
INSERT INTO `treenodes` VALUES ('9', 'I', '8'); 
INSERT INTO `treenodes` VALUES ('10', 'J', '8'); 
INSERT INTO `treenodes` VALUES ('11', 'K', '8'); 
INSERT INTO `treenodes` VALUES ('12', 'L', '9'); 
INSERT INTO `treenodes` VALUES ('13', 'M', '9'); 
INSERT INTO `treenodes` VALUES ('14', 'N', '12'); 
INSERT INTO `treenodes` VALUES ('15', 'O', '12'); 
INSERT INTO `treenodes` VALUES ('16', 'P', '15'); 
INSERT INTO `treenodes` VALUES ('17', 'Q', '15');

把下面的语句直接粘贴进命令行执行即可(注意修改传入的参数,默认rootId,表明默认treenodes)

根据传入id查询所有父节点的id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
delimiter // 
CREATE FUNCTION `getParLst`(rootId INT)
RETURNS varchar(1000) 

BEGIN
	DECLARE sTemp VARCHAR(1000);
	DECLARE sTempPar VARCHAR(1000); 
	SET sTemp = ''; 
	SET sTempPar =rootId; 
	
	#循环递归
	WHILE sTempPar is not null DO 
		#判断是否是第一个,不加的话第一个会为空
		IF sTemp != '' THEN
			SET sTemp = concat(sTemp,',',sTempPar);
		ELSE
			SET sTemp = sTempPar;
		END IF;

		SET sTemp = concat(sTemp,',',sTempPar); 
		SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
	END WHILE; 
	
RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getParList(15));

结果:

id nodename pid
8 H 0
9 I 8
12 L 9
15 O 12

根据传入id查询所有子节点的id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
delimiter // 
CREATE FUNCTION `getParLst`(rootId INT)
RETURNS varchar(1000) 

BEGIN
	DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);

    SET sTemp = '$';
    SET sTempChd =cast(rootId as CHAR);

    WHILE sTempChd is not null DO
    	SET sTemp = concat(sTemp,',',sTempChd);
        SELECT group_concat(id) INTO sTempChd FROM  treeNodes where FIND_IN_SET(pid,sTempChd)>0;
   	END WHILE;
    RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getChildList(7));

结果:

id nodename pid
7 G

本文转载自:https://yq.aliyun.com/articles/48885

共有 人打赏支持
粉丝 0
博文 50
码字总数 13158
作品 0
南京
MySQL递归查找存储过程

依然是SQL问题,是上篇博客(http://my.oschina.net/u/1991646/blog/731996)的升级版需求(上篇是查询3层,此次是查询所有):无可避免的最终还是使用了MySQL存储过程。 先说明业务场景:查...

郁极风
2016/08/16
99
0
无限级分类实现思路 (组织树的分级管理)

关于该问题,暂时自己还没有深入研究,在网上找到几种解决方案,各有优缺点。 第一种方案: 使用递归算法,也是使用频率最多的,大部分开源程序也是这么处理,不过一般都只用到四级分类。这种...

bengozhong
2016/09/09
43
0
MySQL · 新特性分析 · CTE执行过程与实现原理

众所周知,Common table expression(CTE)是在大多数的关系型数据库里都存在的特性,包括ORACLE, SQLSERVER,POSTGRESQL等,唯独开源数据库老大MySQL缺失。CTE作为一个方便用户使用的功能,原本...

阿里云RDS-数据库内核组
2017/02/07
0
0
数据库open遭遇ora-01555错误

数据库open遭遇ora-01555错误 love wife & love life --Roger2017-07-0928 阅读 backupOracle 本站文章除注明转载外,均为本站原创: 转载自 love wife & love life —Roger的Oracle&MySQL技...

love wife & love life --Roger
2017/07/09
0
0
CentOS7 64位安装mysql教程,亲测完美

从最新版本的linux系统开始,默认的是 Mariadb而不是mysql!这里依旧以mysql为例进行展示 1、先检查系统是否装有mysql 这里返回空值,说明没有安装 这里执行安装命令是无效的,因为centos-7默...

编译中ing
10/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

原型模式

1、原型模式-定义 用原型实例指定创建对象的种类,并且通过拷贝这些原型创建新的对象 克隆(浅度克隆->拷贝值类型或者引用,深度克隆->创建新的对象,开辟新的内存) 例如客户端知道抽象Pro...

阿元
今天
10
0
awk命令扩展使用操作

awk 中使用外部shell变量 示例1 [root@centos01 t1022]# A=888[root@centos01 t1022]# echo "" | awk -v GET_A=$A '{print GET_A}'888[root@centos01 t1022]# echo "aaaaaaaaaaaaa" | aw......

野雪球
今天
15
0
深入解析MySQL视图VIEW

Q:什么是视图?视图是干什么用的? A:视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。   通过视图,可以展现基表的部分数据;...

IT--小哥
今天
20
0
虚拟机学习之二:垃圾收集器和内存分配策略

1.对象是否可回收 1.1引用计数算法 引用计数算法:给对象中添加一个引用计数器,每当有一个地方引用它时,计数器值就加1;当引用失效时,计数器值就减1;任何时候计数器值为0的对象就是不可能...

贾峰uk
今天
13
0
smart-doc功能使用介绍

smart-doc从8月份底开始开源发布到目前为止已经迭代了几个版本。在这里非常感谢那些敢于用smart-doc去做尝试并积极提出建议的社区用户。因此决定在本博客中重要说明下smart-doc的功能,包括使...

上官胡闹
昨天
24
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部