-- ----------------------------
-- Table structure for tbtype
-- ----------------------------
DROP TABLE IF EXISTS `tbtype`;
CREATE TABLE `tbtype` (
`typeID` int(11) NOT NULL auto_increment,
`typeName` varchar(20) default NULL,
`typeParent` int(11) NOT NULL,
PRIMARY KEY (`typeID`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tbtype` VALUES ('1', '中国', '0');
INSERT INTO `tbtype` VALUES ('131', '江苏', '1');
INSERT INTO `tbtype` VALUES ('132', '湖北', '1');
INSERT INTO `tbtype` VALUES ('133', '南京', '131');
INSERT INTO `tbtype` VALUES ('134', '无锡', '131');
INSERT INTO `tbtype` VALUES ('135', '武汉', '132');
INSERT INTO `tbtype` VALUES ('136', '南长区', '134');
INSERT INTO `tbtype` VALUES ('137', '新区', '134');
INSERT INTO `tbtype` VALUES ('138', '武昌区', '135');
INSERT INTO `tbtype` VALUES ('139', '江夏区', '135');
数据表如上、如何才能搜出一个节点下的所有子节点的、子子孙孙
+------------+--------------+--------------+
| 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)
参考
http://www.dezai.cn/Article_Show.asp?ArticleID=30633
-- Table structure for tbtype
-- ----------------------------
DROP TABLE IF EXISTS `tbtype`;
CREATE TABLE `tbtype` (
`typeID` int(11) NOT NULL auto_increment,
`typeName` varchar(20) default NULL ,
`typeParent` int(11) NOT NULL,
PRIMARY KEY (`typeID`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=gbk;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tbtype` VALUES ('1', '中国', '0');
INSERT INTO `tbtype` VALUES ('131', '江苏', '1');
INSERT INTO `tbtype` VALUES ('132', '湖北', '1');
INSERT INTO `tbtype` VALUES ('133', '南京', '131');
INSERT INTO `tbtype` VALUES ('134', '无锡', '131');
INSERT INTO `tbtype` VALUES ('135', '武汉', '132');
INSERT INTO `tbtype` VALUES ('136', '南长区', '134');
INSERT INTO `tbtype` VALUES ('137', '新区', '134');
INSERT INTO `tbtype` VALUES ('138', '武昌区', '135');
INSERT INTO `tbtype` VALUES ('139', '江夏区', '135'); delimiter $$drop procedure if exists GetTreeChildren $$create procedure GetTreeChildren(in_typeID INT)
begin
declare l int default 1;
declare cnt int default 0;
create temporary table result(typeID int,typeParent int,typeName varchar(20),level int) engine=memory;
create temporary table tmp(typeID int,level int) engine=memory; insert into result
select typeID,typeParent,typeName,l from tbtype where typeID=in_typeID;
insert into tmp select typeID,level from result; set cnt=row_count();
while(cnt>0) do
set l=l+1; insert into result
select
a.typeID,a.typeParent,a.typename,
l
from tbtype as a
join tmp as b
on a.typeParent=b.typeID
and b.level=l-1;
set cnt=row_count();
insert tmp select typeID,level from result where level=l;
end while; select * from result; drop table result,tmp;
end$$delimiter ;
+--------+----------+------------+
| typeID | typeName | typeParent |
+--------+----------+------------+
| 1 | 中国 | 0 |
| 131 | 江苏 | 1 |
| 132 | 湖北 | 1 |
| 133 | 南京 | 131 |
| 134 | 无锡 | 131 |
| 135 | 武汉 | 132 |
| 136 | 南长区 | 134 |
| 137 | 新区 | 134 |
| 138 | 武昌区 | 135 |
| 139 | 江夏区 | 135 |
+--------+----------+------------+
10 rows in set (0.00 sec)
mysql> CREATE FUNCTION GetAllId (rootid INT) RETURNS varchar(10000)
-> BEGIN
-> DECLARE sFullIDlst varchar(10000);
-> DECLARE idlst varchar(10000);
-> set sFullIDlst = '-1';
-> set idlst=concat(rootid);
->
-> while !isnull(idlst) do
-> set sFullIDlst = concat(sFullIDlst,',',idlst);
-> select group_concat(typeID) into idlst from tbtype where FIND_IN
_SET(typeParent,idlst);
-> end while;
->
-> return sFullIDlst;
->
-> END//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> select * from tbtype
-> where FIND_IN_SET(typeID,GetAllId(1));
+--------+----------+------------+
| typeID | typeName | typeParent |
+--------+----------+------------+
| 1 | 中国 | 0 |
| 131 | 江苏 | 1 |
| 132 | 湖北 | 1 |
| 133 | 南京 | 131 |
| 134 | 无锡 | 131 |
| 135 | 武汉 | 132 |
| 136 | 南长区 | 134 |
| 137 | 新区 | 134 |
| 138 | 武昌区 | 135 |
| 139 | 江夏区 | 135 |
+--------+----------+------------+
10 rows in set (0.02 sec)mysql> select * from tbtype
-> where FIND_IN_SET(typeID,GetAllId(131));
+--------+----------+------------+
| typeID | typeName | typeParent |
+--------+----------+------------+
| 131 | 江苏 | 1 |
| 133 | 南京 | 131 |
| 134 | 无锡 | 131 |
| 136 | 南长区 | 134 |
| 137 | 新区 | 134 |
+--------+----------+------------+
5 rows in set (0.00 sec)