《Mysql必讀mysql遞歸查詢(未分頁版本)》要點：
本文介紹了Mysql必讀mysql遞歸查詢(未分頁版本)，希望對您有用。如果有疑問，可以聯(lián)系我們。
導(dǎo)讀：例子,mysql遞歸查詢代碼.
DROP TABLE IF EXISTS `treenodes`; CREATE TABLE `treenodes` ( `id` int(11) NOT NULL, `nod...
例子,mysql遞歸查詢代碼.
?MYSQL數(shù)據(jù)庫
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遞歸查詢替代函數(shù)實例
mysql遞歸查詢樹形葉子
MySQL 遞歸查詢當前節(jié)點子節(jié)點
mysql遞歸查詢實現(xiàn)辦法
sql遞歸查詢代碼(cte應(yīng)用)
sql2005遞歸查詢的例子
sql遞歸查詢(with cte實現(xiàn))
sql 遞歸查詢的代碼(圖文)
sql server 遞歸查詢數(shù)據(jù)MYSQL數(shù)據(jù)庫
維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql必讀mysql遞歸查詢(未分頁版本)》等實戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/11584.html
