Mysql学习mysql递归查询实例解析

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

导读：《Mysql学习mysql递归查询实例解析》要点：本文介绍了Mysql学习mysql递归查询实例解析，希望对您有用。如果有疑问，可以联系我们。导读：办法1,mysql递归查询./* Navicat MySQL Data Transfer Source Server : mysql_demo3 Source Server Version : 50521

《Mysql学习mysql递归查询实例解析》要点：
本文介绍了Mysql学习mysql递归查询实例解析，希望对您有用。如果有疑问，可以联系我们。

导读：办法1,mysql递归查询. /* Navicat MySQL Data Transfer Source Server : mysql_demo3 Source Server Version : 50521 Sourc...

MYSQL实例办法1,mysql递归查询.
?

/*
Navicat MySQL Data Transfer
?
Source Server???????? : mysql_demo3
Source Server Version : 50521
Source Host?????????? : localhost:3306
Source Database?????? : test
?
Target Server Type??? : MYSQL
Target Server Version : 50521
File Encoding???????? : 65001
??
Date: 2012-09-02 21:16:03
*/
?
SET FOREIGN_KEY_CHECKS=0;
?
-- ----------------------------
-- 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;
?
-- ----------------------------
-- 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');
?

MYSQL实例上边是sql脚本,在执行select * 之后显示的结果集如下：
?

mysql> select * from treenodes;
+----+----------+------+
| id | nodename | pid? |
+----+----------+------+
|? 1 | A??????? |??? 0 |
|? 2 | B??????? |??? 1 |
|? 3 | C??????? |??? 1 |
|? 4 | D??????? |??? 2 |
|? 5 | E??????? |??? 2 |
|? 6 | F??????? |??? 3 |
|? 7 | G??????? |??? 6 |
|? 8 | H??????? |??? 0 |
|? 9 | I??????? |??? 8 |
| 10 | J??????? |??? 8 |
| 11 | K??????? |??? 8 |
| 12 | L??????? |??? 9 |
| 13 | M??????? |??? 9 |
| 14 | N??????? |?? 12 |
| 15 | O??????? |?? 12 |
| 16 | P??????? |?? 15 |
| 17 | Q??????? |?? 15 |
+----+----------+------+
17 rows in set (0.00 sec)
?

MYSQL实例树形图：
?

1:A
? +-- 2:B
? |??? +-- 4:D
? |??? +-- 5:E
? +-- 3:C
?????? +-- 6:F
??????????? +-- 7:G
8:H
? +-- 9:I
? |??? +-- 12:L
? |??? |??? +--14:N
? |??? |??? +--15:O
? |??? |??????? +--16:P
? |??? |??????? +--17:Q
? |??? +-- 13:M
? +-- 10:J
? +-- 11:K??
?

MYSQL实例如果给你一个这样的table,让你查询根节点为1下的所有节点记录(注意也包括根节点)?
可能有不少人想到connect by 函数,但是我灰常遗憾的告诉你,咱这儿是mysql!
?
解决办法：利用函数来得到所有子节点号.
?
闲话少续,看我的解决办法
创建一个function getChildLst,得到一个由所有子节点号组成的字符串.?
?

mysql> delimiter //
mysql>
mysql> CREATE FUNCTION `getChildLst`(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
??? ;
Query OK,0 rows affected (0.00 sec)
?
mysql>
mysql> delimiter ;
?

MYSQL实例?
使用我们直接利用find_in_set函数配合这个getChildlst来查找：
?

mysql> select getChildLst(1);
+-----------------+
| getChildLst(1)? |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in set (0.00 sec)
?
mysql> select * from treeNodes
??? -> where FIND_IN_SET(id,getChildLst(1));
+----+----------+------+
| id | nodename | pid? |
+----+----------+------+
|? 1 | A??????? |??? 0 |
|? 2 | B??????? |??? 1 |
|? 3 | C??????? |??? 1 |
|? 4 | D??????? |??? 2 |
|? 5 | E??????? |??? 2 |
|? 6 | F??????? |??? 3 |
|? 7 | G??????? |??? 6 |
+----+----------+------+
7 rows in set (0.01 sec)
?
mysql> select * from treeNodes
??? -> where FIND_IN_SET(id,getChildLst(3));
+----+----------+------+
| id | nodename | pid? |
+----+----------+------+
|? 3 | C??????? |??? 1 |
|? 6 | F??????? |??? 3 |
|? 7 | G??????? |??? 6 |
+----+----------+------+
3 rows in set (0.01 sec)
?

MYSQL实例只要按我的做,百发百中百步穿杨,遇到问题万变不离其宗直接粘贴复制就是.
?
补充：
还可以做嵌套查询：
?

select id,pid from treeNodes where id in(
???? select id from treeNodes where FIND_IN_SET(id,getChildLst(3))
);
?

MYSQL实例子查询的结果集是：?
+--------+
id
----
3
6
7
+-------+
然后,经过外层查询就是：
id? pid
3?? 1
6?? 3
6?? 6
---------
mysql递归查询替代函数实例
mysql递归查询树形叶子
Oracle递归查询树形结构
MySQL 递归查询当前节点子节点
mysql递归查询实现办法
Oracle递归查询SQL语句分享
sql2005递归查询的例子
sql递归查询(with cte实现)
sql 递归查询的代码(图文)

编程之家PHP培训学院每天发布《Mysql学习mysql递归查询实例解析》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。

（编辑：李大同）

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