mysql递归查询查不全_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 递归查询数据