有表:protype
字段有:id(主键),typename,fatherid(上级目录id)
如有数据:
id typename fatherid
1 a 0
2 b 0
3 c 0
4 d 1
5 e 2
6 f 3即当:fatherid为0是为上级id否则为指定目录
如:d的上级目录是a;e的上级目录是b;f的上级目录是c
问题是我改如何写sql语句:让其显示在页面时有个菜单式目录结构!
形如:
a
->d
b
->e
c
->f
????????在线等候……
字段有:id(主键),typename,fatherid(上级目录id)
如有数据:
id typename fatherid
1 a 0
2 b 0
3 c 0
4 d 1
5 e 2
6 f 3即当:fatherid为0是为上级id否则为指定目录
如:d的上级目录是a;e的上级目录是b;f的上级目录是c
问题是我改如何写sql语句:让其显示在页面时有个菜单式目录结构!
形如:
a
->d
b
->e
c
->f
????????在线等候……
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
mysql> delimiter //
mysql>
mysql> # 入口过程
mysql> CREATE PROCEDURE showChildLst (IN rootId INT)
-> 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;
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> # 递归过程
mysql> 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;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;调用时传入结点
mysql> call showChildLst(1);
+-----+------+-------+----+----------+------+
| sno | id | depth | id | nodename | pid |
+-----+------+-------+----+----------+------+
| 4 | 1 | 0 | 1 | A | 0 |
| 5 | 2 | 1 | 2 | B | 1 |
| 6 | 4 | 2 | 4 | D | 2 |
| 7 | 5 | 2 | 5 | E | 2 |
| 8 | 3 | 1 | 3 | C | 1 |
| 9 | 6 | 2 | 6 | F | 3 |
| 10 | 7 | 3 | 7 | G | 6 |
+-----+------+-------+----+----------+------+7 rows in set (0.13 sec)Query OK, 0 rows affected, 1 warning (0.14 sec)mysql>
mysql> call showChildLst(3);
+-----+------+-------+----+----------+------+
| sno | id | depth | id | nodename | pid |
+-----+------+-------+----+----------+------+
| 1 | 3 | 0 | 3 | C | 1 |
| 2 | 6 | 1 | 6 | F | 3 |
| 3 | 7 | 2 | 7 | G | 6 |
+-----+------+-------+----+----------+------+3 rows in set (0.11 sec)Query OK, 0 rows affected, 1 warning (0.11 sec)depth 为深度,这样可以在程序进行一些显示上的格式化处理。类似于oracle中的 level 伪列。sno 仅供排序控制。这样你还可以通过临时表tmpLst与数据库中其它表进行联接查询。MySQL中你可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。如下例设为12.
mysql> set max_sp_recursion_depth=12;
Query OK, 0 rows affected (0.00 sec)优点 : 可以更灵活处理,及层数的显示。并且可以按照树的遍历顺序得到结果。缺点 : 递归有255的限制。