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