56,679
社区成员
发帖
与我相关
我的任务
分享
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)