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>