56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE showChildLst (IN rootId INT)
-> BEGIN
-> CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno int primary key auto_in
crement,id int,depth int);
-> DELETE FROM tmpLst;
->
-> CALL createChildLst(rootId,0);
->
-> select concat('|',REPEAT('-', tmpLst.depth),table1.name)
-> from tmpLst,table1
-> where tmpLst.id=table1.id
-> order by tmpLst.sno;
->
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
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 table1 where parent=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.01 sec)
mysql>
mysql> delimiter ;
mysql> select * from table1;
+----+--------+-------+
| id | parent | name |
+----+--------+-------+
| 1 | 0 | node1 |
| 2 | 1 | node2 |
| 3 | 1 | node3 |
| 4 | 0 | node4 |
| 5 | 2 | node5 |
+----+--------+-------+
5 rows in set (0.01 sec)
mysql> set max_sp_recursion_depth=12;
Query OK, 0 rows affected (0.05 sec)
mysql> call showChildLst(0);
+---------------------------------------------------+
| concat('|',REPEAT('-', tmpLst.depth),table1.name) |
+---------------------------------------------------+
| |-node1 |
| |--node2 |
| |---node5 |
| |--node3 |
| |-node4 |
+---------------------------------------------------+
5 rows in set (0.13 sec)
Query OK, 0 rows affected (0.14 sec)
mysql>
mysql> select * from tb_test;
+------------+--------------+--------------+
| ProdCateID | ProdCateName | ParentCateID |
+------------+--------------+--------------+
| 1 | 服装 | 0 |
| 2 | 箱包 | 0 |
| 3 | 内衣 | 1 |
| 4 | 外套 | 1 |
| 5 | 男箱包 | 2 |
| 6 | 女箱包 | 2 |
| 7 | 内裤 | 3 |
| 8 | 文胸 | 3 |
| 9 | 男外套 | 4 |
| 10 | 女大衣 | 4 |
| 11 | 男用钱包 | 5 |
| 12 | 女用钱包 | 6 |
+------------+--------------+--------------+
SP代码如下:
DELIMITER $$
DROP PROCEDURE IF EXISTS `tennis`.`sp_tree_test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_tree_test`(in parent_id int)
begin
declare level smallint default 0;
declare cnt int default 0;
create temporary table tt(ProdCateID int,ProdCateName varchar(20),
ParentCateID int,level smallint,sort varchar(1000));
create temporary table tt2(ProdCateID int,ProdCateName varchar(20),
ParentCateID int,level smallint,sort varchar(1000));
insert into tt select ProdCateID,ProdCateName,
ParentCateID,level,cast(ProdCateID as char)
from tb_test
where ParentCateID=parent_id;
select row_count() into cnt;
insert into tt2 select * from tt;
while cnt>0 do
set level=level+1;
truncate table tt;
insert into tt select a.ProdCateID,a.ProdCateName,
a.ParentCateID,level,concat(b.sort,a.ProdCateID)
from tb_test a,tt2 b
where a.ParentCateID=b.ProdCateID and b.level=level-1;
select row_count() into cnt;
insert into tt2 select * from tt;
end while;
select ProdCateID,
concat(space(a.level*2),'|--',a.ProdCateName) ProdCateName
from tt2 a
order by sort;
drop table tt;
drop table tt2;
end $$
DELIMITER ;
##执行
mysql> call sp_tree_test(0);
+------------+-----------------+
| ProdCateID | ProdCateName |
+------------+-----------------+
| 1 | |--服装 |
| 3 | |--内衣 |
| 7 | |--内裤 |
| 8 | |--文胸 |
| 4 | |--外套 |
| 10 | |--女大衣 |
| 9 | |--男外套 |
| 2 | |--箱包 |
| 5 | |--男箱包 |
| 11 | |--男用钱包 |
| 6 | |--女箱包 |
| 12 | |--女用钱包 |
+------------+-----------------+
12 rows in set (0.30 sec)