Mysql必读mysql递归查询(未分页版本)

发布时间：2020-12-12 02:47:47 所属栏目：MySql教程来源：网络整理

导读：《Mysql必读mysql递归查询(未分页版本)》要点：本文介绍了Mysql必读mysql递归查询(未分页版本)，希望对您有用。如果有疑问，可以联系我们。导读：例子,mysql递归查询代码.DROP TABLE IF EXISTS `treenodes`; CREATE TABLE `treenodes` ( `id` int(11) NOT

《Mysql必读mysql递归查询(未分页版本)》要点：
本文介绍了Mysql必读mysql递归查询(未分页版本)，希望对您有用。如果有疑问，可以联系我们。

导读：例子,mysql递归查询代码. DROP TABLE IF EXISTS `treenodes`; CREATE TABLE `treenodes` ( `id` int(11) NOT NULL,`nod...

例子,mysql递归查询代码.
?MYSQL数据库

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=utf8;?
?
-- ----------------------------?
-- 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');?
?
CREATE PROCEDURE showChildList (IN rootId INT,IN)?
?
BEGIN?
?? CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst?
?? (sno int primary key auto_increment,?
??? id int,?
??? depth int?
?? );?
?? DELETE FROM tmpLst;?
?
?
?? CALL createChildLst(rootId,0);?
?
?? select tmpLst.*,treeNodes.*?
?? from tmpLst,treeNodes?
?? where tmpLst.id = treeNodes.id??
?? order by tmpLst.sno;?
?
END;
?
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)?
BEGIN?
????? DECLARE done INT DEFAULT 0;?
????? DECLARE b INT;?
????? DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;?
????? DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;?
?????
????? insert into tmpLst values (null,rootId,nDepth);
?????
????? OPEN cur1;?
?????
????? FETCH cur1 INTO b;?
????? WHILE done=0 DO?
????????????? CALL createChildLst(b,nDepth+1);?
????????????? FETCH cur1 INTO b;?
????? END WHILE;?
?????
????? CLOSE cur1;?
END;?
?
mysql> call showChildList(1);?
+-----+----+-------+----+----------+-----+?
| sno | id | depth | id | nodename | pid |?
+-----+----+-------+----+----------+-----+?
|?? 2 |? 1 |???? 0 |? 1 | A??????? |?? 0 |?
|?? 3 |? 2 |???? 1 |? 2 | B??????? |?? 1 |?
|?? 4 |? 4 |???? 2 |? 4 | D??????? |?? 2 |?
|?? 5 |? 5 |???? 2 |? 5 | E??????? |?? 2 |?
|?? 6 |? 3 |???? 1 |? 3 | C??????? |?? 1 |?
|?? 7 |? 6 |???? 2 |? 6 | F??????? |?? 3 |?
|?? 8 |? 7 |???? 3 |? 7 | G??????? |?? 6 |?
+-----+----+-------+----+----------+-----+?
7 rows in set?
?
Query OK,0 rows affected?
?
mysql> call showChildList(3);?
+-----+----+-------+----+----------+-----+?
| sno | id | depth | id | nodename | pid |?
+-----+----+-------+----+----------+-----+?
|?? 9 |? 3 |???? 0 |? 3 | C??????? |?? 1 |?
|? 10 |? 6 |???? 1 |? 6 | F??????? |?? 3 |?
|? 11 |? 7 |???? 2 |? 7 | G??????? |?? 6 |?
+-----+----+-------+----+----------+-----+?
3 rows in set?
?
Query OK,0 rows affected?
?
mysql> call showChildList(5);?
+-----+----+-------+----+----------+-----+?
| sno | id | depth | id | nodename | pid |?
+-----+----+-------+----+----------+-----+?
|? 12 |? 5 |???? 0 |? 5 | E??????? |?? 2 |?
+-----+----+-------+----+----------+-----+?
1 row in set?
?
Query OK,0 rows affected

mysql递归查询替代函数实例
mysql递归查询树形叶子
MySQL 递归查询当前节点子节点
mysql递归查询实现办法
sql递归查询代码(cte应用)
sql2005递归查询的例子
sql递归查询(with cte实现)
sql 递归查询的代码(图文)
sql server 递归查询数据MYSQL数据库

编程之家PHP培训学院每天发布《Mysql必读mysql递归查询(未分页版本)》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!