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>