Oracle 循环递归遍历树结构查询
2015年10月27日 16:38:26 vrenzhuv 阅读数：2831 标签： oracle递归遍历 更多
个人分类： Oracle
在项目中经常会接触树结构的页面； 经常会写关于查询某个树节点的所有子节点的方法，若使用java的for循环或者递归不但效率低又好性能。所以推荐使用oracle 提供的connect by prior start with 的递归查询用法。
oracle的connect by prior start with 是个双向查询树结构的功能，既可以查询子节点 ，又可以根据子节点查询上流的所有父节点； 举例如下:
准备的SQL：
-
drop table t_tmp_20151027; -
create table t_tmp_20151027( -
code varchar2(10) primary key, -
name varchar2(50), -
parent_code varchar2(10), -
create_time date default sysdate, -
active char(1) default 'Y' -
); -
comment on column t_tmp_20151027.code is '部门编码'; -
comment on column t_tmp_20151027.name is '部门名称'; -
comment on column t_tmp_20151027.parent_code is '父级编码'; -
comment on column t_tmp_20151027.create_time is '创建日期 默认sysdate'; -
comment on column t_tmp_20151027.active is '是否有效(Y有效,默认)'; -
select * from t_tmp_20151027; -
insert into t_tmp_20151027(code, name, parent_code) values('D00001', '总裁办公室', null); -
insert into t_tmp_20151027(code, name, parent_code) values('D01000', '企业发展办公室', 'D00001'); -
insert into t_tmp_20151027(code, name, parent_code) values('D10000', '职能运营本部', 'D01000'); -
insert into t_tmp_20151027(code, name, parent_code) values('D10001', '华东运行本部', 'D10000'); -
insert into t_tmp_20151027(code, name, parent_code) values('D10002', '华南运行本部', 'D10000'); -
insert into t_tmp_20151027(code, name, parent_code) values('D20000', '信息技术部', 'D01000'); -
insert into t_tmp_20151027(code, name, parent_code) values('D20001', 'IT开发部', 'D20000'); -
insert into t_tmp_20151027(code, name, parent_code) values('D20002', '运维管理部', 'D20000');
具体用法:
-
--通过父节点查询此节点及向下递归遍历所有的子节点... -
select a.* -
from t_tmp_20151027 a, -
(select distinct code -
from t_tmp_20151027 -
where active = 'Y' -
connect by prior code = parent_code -
and active = 'Y' -
start with code = 'D00001') b -
where 1 = 1 -
and a.code = b.code -
and a.active = 'Y'; -
--通过子节点查询此节点及向上递归遍历所有的父节点 -
select a.* -
from t_tmp_20151027 a, -
(select distinct code -
from t_tmp_20151027 -
where active = 'Y' -
connect by prior parent_code = code -
and active = 'Y' -
start with code = 'D20000') b -
where 1 = 1 -
and a.code = b.code -
and a.active = 'Y';
https://blog.csdn.net/vrenzhuv/article/details/49450457
您的位置：首页 → 数据库 → Mysql → 正文内容 数据库实现树形的遍历
关注微信公众号
在Mysql数据库里通过存储过程实现树形的遍历
更新时间：2016年11月02日 14:30:09 投稿：mrr 
我要评论
关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面给大家介绍在Mysql数据库里通过存储过程实现树形的遍历，一起看看吧
关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。
1，建立测试表和数据：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
2，利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：
2.1，从某节点向下遍历子节点，递归生成临时表数据
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|
2.2，从某节点向上追溯根节点，递归生成临时表数据
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
|
2.3，实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
|
2.4，递归过程输出某节点name路径
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
|
2.5，调用函数输出id路径
| 1 2 3 4 5 6 7 8 9 10 |
|
2.6，调用函数输出name路径
| 1 2 3 4 5 6 7 8 9 10 11 12 |
|
2.7，调用过程输出子节点
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
2.8，调用过程输出父节点
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
3，开始测试：
3.1，从根节点开始显示，显示子节点集合：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
3.2，显示首页下面的子节点
| 1 2 3 4 5 6 7 8 9 10 11 |
|
3.3，显示TV580下面的所有子节点
| 1 2 3 4 5 6 7 8 9 |
|
3.4，“帮忙”节点有一个子节点，显示出来：
| 1 2 3 4 5 6 7 8 |
|
3.5，“栏目简介”没有子节点，所以只显示最终节点：
| 1 2 3 4 5 6 |
|
3.6，显示根节点的父节点
| 1 2 3 4 5 |
|
3.7，显示“首页”的父节点
| 1 2 3 4 5 6 7 |
|
3.8，显示“TV580”的父节点，parent_id为-1
| 1 2 3 4 5 6 |
|
3.9，显示“帮忙”节点的父节点
| 1 2 3 4 5 6 7 8 |
|
3.10，显示最低层节点“栏目简介”的父节点
| 1 2 3 4 5 6 7 8 9 |
|
以上所述是小编给大家介绍的在Mysql数据库里通过存储过程实现树形的遍历，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！
您可能感兴趣的文章:
- mysql存储过程详解
- mysql存储过程 游标 循环使用介绍
- MySQL存储过程使用实例详解
- MySQL存储过程例子(包含事务，输出参数，嵌套调用)
- MySql存储过程与函数详解
- MySQL 实现树的遍历详解及简单实现示例
- mysql存储过程基础之遍历多表记录后插入第三方表中详解
https://www.jb51.net/article/96317.htm
随笔- 67 文章- 0 评论- 8
MySQL递归查询所有子节点，树形结构查询
--表结构
CREATE TABLE `address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code_value` varchar(32) DEFAULT NULL COMMENT '区域编码',
`name` varchar(128) DEFAULT NULL COMMENT '区域名称',
`remark` varchar(128) DEFAULT NULL COMMENT '说明',
`pid` varchar(32) DEFAULT NULL COMMENT 'pid是code_value',
PRIMARY KEY (`id`),
KEY `ix_name` (`name`,`code_value`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1033 DEFAULT CHARSET=utf8 COMMENT='行政区域表';
--mysql 实现树结构查询
--方法一
CREATE PROCEDURE sp_showChildLst(IN rootId varchar(20))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,code_value VARCHAR(20),depth int);
DELETE FROM tmpLst;
CALL sp_createChildLst(rootId,0);
select tmpLst.*,address.* from tmpLst,address where tmpLst.code_value=address.code_value order by tmpLst.sno;
END
CREATE PROCEDURE sp_createChildLst(IN rootId varchar(20),IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT code_value FROM address where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
SET @@max_sp_recursion_depth = 10;
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL sp_createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END
--方法二（此方法有线程安全问题）
CREATE PROCEDURE sp_getAddressChild_list(in idd varchar(36))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(code_value VARCHAR(36),`name` varchar(50),pid varchar(36) ,levv INT);
INSERT tmp1 SELECT code_value,`name`,pid,1 FROM address WHERE pid=idd;
while row_count()>0
do
set lev=lev+1;
INSERT tmp1 SELECT t.code_value,t.`name`,t.pid,lev from address t join tmp1 a on t.pid=a.code_value AND levv=lev-1;
end while ;
INSERT tmp1 SELECT code_value,`name`,pid,0 FROM address WHERE code_value=idd;
SELECT * FROM tmp1;
end
--方法三
CREATE FUNCTION fn_getAddress_ChildList_test(rootId INT) RETURNS varchar(1000) CHARSET utf8 #rootId为你要查询的节点
BEGIN
#声明两个临时变量
DECLARE temp VARCHAR(1000);
DECLARE tempChd VARCHAR(1000);
SET temp = '$';
SET tempChd=CAST(rootId AS CHAR);#把rootId强制转换为字符
WHILE tempChd is not null DO
SET temp = CONCAT(temp,',',tempChd);#循环把所有节点连接成字符串。
SELECT GROUP_CONCAT(code_value) INTO tempChd FROM address where FIND_IN_SET(pid,tempChd)>0;
END WHILE;
RETURN temp;
END
--方法四
CREATE PROCEDURE sp_findAddressChild(iid varchar(50),layer bigint(20))
BEGIN
/*创建接受查询的临时表 */
create temporary table if not exists tmp_table(id varchar(50),code_value varchar(50),name varchar(50),pid VARCHAR(50)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*最高允许递归数*/
SET @@max_sp_recursion_depth = 10 ;
call sp_iterativeAddress(iid,layer);/*核心数据收集*/
select * from tmp_table ;/* 展现 */
drop temporary table if exists tmp_table ;/*删除临时表*/
END
CREATE PROCEDURE sp_iterativeAddress(iid varchar(50),layer bigint(20))
BEGIN
DECLARE t_id INT;
declare t_codeValue varchar(50) default iid ;
declare t_name varchar(50) character set utf8;
declare t_pid varchar(50) character set utf8;
/* 游标定义 */
declare cur1 CURSOR FOR select id,code_value,`name`,pid from address where pid=iid ;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET t_codeValue = null;
/* 允许递归深度 */
if layer>0 then
OPEN cur1 ;
FETCH cur1 INTO t_id,t_codeValue,t_name,t_pid ;
WHILE ( t_codeValue is not null )
DO
/* 核心数据收集 */
insert into tmp_table values(t_id,t_codeValue,t_name,t_pid);
call sp_iterativeAddress(t_codeValue,layer-1);
FETCH cur1 INTO t_id,t_codeValue,t_name,t_pid ;
END WHILE;
end if;
END
--方法五 SQL实现
SELECT `name`,code_value AS code_value,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM (
SELECT `name`,code_value,pid,
@le:= IF (pid = 0 ,0,
IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0 ,
SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1
,@le+1) ) levels
, @pathlevel:= CONCAT(@pathlevel,'|',code_value,':', @le ,'|') pathlevel
, @pathnodes:= IF( pid =0,',0',
CONCAT_WS(',',
IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,
SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1)
,@pathnodes ) ,pid ) )paths
,@pathall:=CONCAT(@pathall,'|',code_value,':', @pathnodes ,'|') pathall
FROM address,
(SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
ORDER BY pid,code_value
) src
ORDER BY pid
--方法6 存储过程（SQL实现）
create procedure query_all_add_children(in inPid varchar(50))
begin
select id,code_value,name,remark,pid,p2id,p3id,p4id,p5id
from(
select a1.id,a1.code_value,a1.name,a1.remark,
a1.pid,a2.pid p2id,a3.pid p3id,a4.pid p4id,a5.pid p5id
from
address a1 left join address a2
on(a1.pid=a2.code_value)
left join address a3
on(a2.pid=a3.code_value)
left join address a4
on(a3.pid=a4.code_value)
left join address a5
on(a4.pid=a5.code_value)
) al
where
(pid=inPid
or p2id=inPid
or p3id=inPid
or p4id=inPid
or p5id=inPid
);
end
个人的一些理解：我是用的方法一：取出所有节点利用MySql函数截取所需要的字符串，然后在SQL中字段IN（调用此方法）来进行查询，这样效率比较高，方法6效率也较高，其他方法都有效率问题。
Mysql学习
标签: Mysql
+加关注
0
0
« 上一篇：navicat 快捷键
» 下一篇：Linux(Centos、Debian)之安装Java JDK及注意事项（转）
posted @ 2016-06-15 17:02 MrZhaoFei 阅读(36836) 评论(1) 编辑 收藏
https://www.cnblogs.com/zf29506564/p/5588153.html
原
Mysql无限递归树(由子找父数据父名称)
2016年03月06日 12:32:20 yayue1573 阅读数：4037
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/yayue1573/article/details/50812804
接到一个需求，就是订单找找到它的所有推广关系，把推广人的名字按层级导出来，找了好久，mysql没有支持无限递归树的函数，查了资料转换为自己要的查询，了解了一次mysql的函数，感觉棒棒哒
-
DROP FUNCTION IF EXISTS getParentList; -
CREATE FUNCTION `getParentList`(rootId INT) -
RETURNS varchar(1000) -
BEGIN -
#定义变量 -
DECLARE sParentList varchar(1000); -
DECLARE sParentTemp varchar(1000); -
DECLARE sParentName varchar(1000); -
DECLARE sParentTmpName varchar(1000); -
DECLARE sParentTmpMobile varchar(1000); -
#将入参强制转换为字符串 -
SET sParentTemp = cast(rootId as CHAR); -
WHILE sParentTemp is not null DO #当条件为不为空的时候执行(找不到上级就可以执行) -
SET sParentTmpName = ( SELECT true_name FROM zjj_user.user_profile where user_id = sParentTemp ); #查找你需要的字段 -
IF (sParentList is not null) THEN #判断只有一层的关系 -
SET sParentList = concat(sParentTemp,',',sParentList); -
SET sParentName = CONCAT(sParentTmpName,',',sParentName); #将你需要输出的字段进行拼接 -
ELSE -
SET sParentList = concat(sParentTemp); -
SET sParentName = concat(sParentTmpName); -
END IF; -
SELECT group_concat(p_uid) INTO sParentTemp FROM user_introduce_relation where user_id = sParentTemp; #将变量替换成找到的值 -
END WHILE; -
RETURN sParentName; #返回需要的 -
END; -
select getParentList(1123);
我的需求查找到的结果
https://blog.csdn.net/yayue1573/article/details/50812804?utm_source=blogxgwz0
随笔-1978 评论-104 文章-4
无限级分类实现思路 (组织树的分级管理)
关于该问题，暂时自己还没有深入研究，在网上找到几种解决方案，各有优缺点。
第一种方案：
使用递归算法，也是使用频率最多的，大部分开源程序也是这么处理，不过一般都只用到四级分类。这种算法的数据库结构设计最为简单。category表中一个字段id，一个字段fid(父id)。这样可以根据WHERE id = fid来判断上一级内容，运用递归至最顶层。
分析：通过这种数据库设计出的无限级，可以说读取的时候相当费劲，所以大部分的程序最多3-4级分类，这就足以满足需求，从而一次性读出所有的数据，再对得到数组或者对象进行递归。本身负荷还是没太大问题。但是如果分类到更多级，那是不可取的办法。
这样看来这种分类有个好处，就是增删改的时候轻松了…然而就二级分类而言，采用这种算法就应该算最优先了。
在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾，在MySQL的目前版本中还没有对应的功能。
在MySQL中如果是有限的层次，比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4，则我们可以直接通过left join 来实现。
但很多时候我们无法控制树的深度。这时就需要在MySQL中用存储过程来实现或在你的程序中来实现这个递归。本文讨论一下几种实现的方法。
样例数据：
CREATE TABLE treeNodes
(
id INT PRIMARY KEY,
nodename VARCHAR(20),
pid INT
);mysql> select * from treenodes;
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 1 | A | 0 |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 6 |
| 8 | H | 0 |
| 9 | I | 8 |
| 10 | J | 8 |
| 11 | K | 8 |
| 12 | L | 9 |
| 13 | M | 9 |
| 14 | N | 12 |
| 15 | O | 12 |
| 16 | P | 15 |
| 17 | Q | 15 |
+----+----------+------+
17 rows in set (0.00 sec)
树形图如下
1:A
+-- 2:B
| +-- 4:D
| +-- 5:E
+-- 3:C
+-- 6:F
+-- 7:G
8:H
+-- 9:I
| +-- 12:L
| | +--14:N
| | +--15:O
| | +--16:P
| | +--17:Q
| +-- 13:M
+-- 10:J
+-- 11:K
实现方法示例：
方法一：利用函数来得到所有子节点号。
创建一个function getChildLst, 得到一个由所有子节点号组成的字符串.
delimiter //
CREATE FUNCTION `getChildList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
//
delimiter ;
获取所有父节点：
delimiter //
CREATE FUNCTION `getParentList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sParentList varchar(1000);
DECLARE sParentTemp varchar(1000);
SET sParentTemp =cast(rootId as CHAR);
WHILE sParentTemp is not null DO
IF (sParentList is not null) THEN
SET sParentList = concat(sParentTemp,',',sParentList);
ELSE
SET sParentList = concat(sParentTemp);
END IF;
SELECT group_concat(pid) INTO sParentTemp FROM treeNodes where FIND_IN_SET(id,sParentTemp)>0;
END WHILE;
RETURN sParentList;
END
//
delimiter ;
/*获取父节点*/
/*调用: 1、select getParentList(6) id; 2、select * From user_role where FIND_IN_SET(id, getParentList(2));*/
select getParentList(4);
使用我们直接利用find_in_set函数配合这个getChildlst来查找
mysql> select getChildList(1);
+-----------------+
| getChildLst(1) |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from treeNodes
-> where FIND_IN_SET(id, getChildList(1));
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 1 | A | 0 |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 6 |
+----+----------+------+
7 rows in set (0.01 sec)
mysql> select * from treeNodes
-> where FIND_IN_SET(id, getChildList(3));
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 3 | C | 1 |
| 6 | F | 3 |
| 7 | G | 6 |
+----+----------+------+
3 rows in set (0.01 sec)
优点: 简单，方便，没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255) ；
缺点：长度受限，虽然可以扩大 RETURNS varchar(1000)，但总是有最大限制的。
MySQL目前版本( 5.1.33-community)中还不支持function 的递归调用。
方法二：利用临时表和过程递归
创建存储过程如下。createChildLst 为递归过程，showChildLst为调用入口过程，准备临时表及初始化。
DELIMITER //
# 入口过程
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;
//
DELIMITER ;
DELIMITER //
# 递归过程
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;
//
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的限制。
方法三：利用中间表和过程
(本方法由yongyupost2000提供样子改编)
创建存储过程如下。由于MySQL中不允许在同一语句中对临时表多次引用，只以使用普通表tmpLst来实现了。当然你的程序中负责在用完后清除这个表。
DELIMITER //
DROP PROCEDURE IF EXISTS showTreeNodes_yongyupost2000//
CREATE PROCEDURE showTreeNodes_yongyupost2000 (IN rootid INT)
BEGIN
DECLARE LEVEL INT ;
DROP TABLE IF EXISTS tmpLst;
CREATE TABLE tmpLst (
id INT,
nLevel INT,
sCort VARCHAR(8000)
);
SET LEVEL=0 ;
INSERT INTO tmpLst SELECT id,LEVEL,ID FROM treeNodes WHERE PID=rootid;
WHILE ROW_COUNT()>0 DO
SET LEVEL=LEVEL+1 ;
INSERT INTO tmpLst
SELECT A.ID,LEVEL,CONCAT(B.sCort,A.ID) FROM treeNodes A,tmpLst B
WHERE A.PID=B.ID AND B.nLevel=LEVEL-1 ;
END WHILE;
SELECT tmpLst.*,treeNodes.* FROM tmpLst,treeNodes WHERE tmpLst.id=treeNodes.id ORDER BY tmpLst.id;
END;
//
DELIMITER ;
CALL showTreeNodes_yongyupost2000(1);
执行完后会产生一个tmpLst表，nLevel 为节点深度，sCort 为排序字段。
使用方法
SELECT concat(SPACE(B.nLevel*2),'+--',A.nodename)
FROM treeNodes A,tmpLst B
WHERE A.ID=B.ID
ORDER BY B.sCort;
+--------------------------------------------+
| concat(SPACE(B.nLevel*2),'+--',A.nodename) |
+--------------------------------------------+
| +--A |
| +--B |
| +--D |
| +--E |
| +--C |
| +--F |
| +--G |
| +--H |
| +--J |
| +--K |
| +--I |
| +--L |
| +--N |
| +--O |
| +--P |
| +--Q |
| +--M |
+--------------------------------------------+
17 rows in set (0.00 sec)
优点 : 层数的显示。并且可以按照树的遍历顺序得到结果。没有递归限制。
缺点 : MySQL中对临时表的限制，只能使用普通表，需做事后清理。
以上是几个在MySQL中用存储过程比较简单的实现方法。
第二种方案：
设置fid字段类型为varchar，将父类id都集中在这个字段里，用符号隔开，比如：1,3,6
这样可以比较容易得到各上级分类的ID，而且在查询分类下的信息的时候，
可以使用：SELECT * FROM category WHERE pid LIKE “1,3%”。
分析：相比于递归算法，在读取数据方面优势非常大，但是若查找该分类的所有 父分类 或者 子分类 查询的效率也不是很高，至少也要二次query，从某种意义看上，个人觉得不太符合数据库范式的设计。倘若递增到无限级，还需考虑字段是否达到要求，而且在修改分类和转移分类的时候操作将非常麻烦。
暂时，在自己项目中用的就是类似第二种方案的解决办法。就该方案在我的项目中存在这样的问题， 如果当所有数据记录达到上万甚至10W以上后，一次性将所以分类，有序分级的现实出来，效率很低。极有可能是项目处理数据代码效率低带来的。现在正在改良。
第三种方案：
无限级分类----改进前序遍历树
那么理想中的树型结构应具备哪些特点呢？数据存储冗余小、直观性强；方便返回整个树型结构数据；可以很轻松的返回某一子树（方便分层加载）；快整获以某节点的祖谱路径；插入、删除、移动节点效率高等等。带着这些需求我查找了很多资料，发现了一种理想的树型结构数据存储及操作算法，改进的前序遍历树模型（The Nested Set Model）。
原理：
我们先把树按照水平方式摆开。从根节点开始（“Food”），然后他的左边写上1。然后按照树的顺序（从上到下）给“Fruit”的左边写上2。这样，你沿着树的边界走啊走（这就是“遍历”），然后同时在每个节点的左边和右边写上数字。最后，我们回到了根节点“Food”在右边写上18。下面是标上了数字的树，同时把遍历的顺序用箭头标出来了。 
我们称这些数字为左值和右值（如，“Food”的左值是1，右值是18）。正如你所见，这些数字按时了每个节点之间的关系。因为“Red”有3和6两个值，所以，它是有拥有1-18值的“Food”节点的后续。同样的，我们可以推断所有左值大于2并且右值小于11的节点，都是有2-11的“Fruit” 节点的后续。这样，树的结构就通过左值和右值储存下来了。这种数遍整棵树算节点的方法叫做“改进前序遍历树”算法。
表结构设计：
undefined那么我们怎样才能通过一个SQL语句把所有的分类都查询出来呢，而且要求如果是子类的话前面要打几个空格以表现是子分类。要想查询出所有分类很好办：SELECT * FROM category WHERE lft>1 AND lft<18 ORDER BY lft这样的话所有的分类都出来了，但是谁是谁的子类却分不清，那么怎么办呢?我们仔细看图不难发现如果相邻的两条记录的右值第一条的右值比第二条的大那么就是他的父类，比如food的右值是18而fruit的右值是11 那么food是fruit的父类，但是又要考虑到多级目录。于是有了这样的设计，我们用一个数组来存储上一条记录的右值，再把它和本条记录的右值比较，如果前者比后者小，说明不是父子关系，就用array_pop弹出数组，否则就保留，之后根据数组的大小来打印空格。这样就解决了这个问题。代码如下
表结构：
--
-- 表的结构 `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL COMMENT '1为文章类型2为产品类型3为下载类型',
`title` varchar(50) NOT NULL,
`lft` int(11) NOT NULL,
`rgt` int(11) NOT NULL,
`lorder` int(11) NOT NULL COMMENT '排序',
`create_time` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
--
-- 导出表中的数据 `category`
--
INSERT INTO `category` (`id`, `type`, `title`, `lft`, `rgt`, `lorder`, `create_time`) VALUES
(1, 1, '顶级栏目', 1, 18, 1, 1261964806),
(2, 1, '公司简介', 14, 17, 50, 1264586212),
(3, 1, '新闻', 12, 13, 50, 1264586226),
(4, 2, '公司产品', 10, 11, 50, 1264586249),
(5, 1, '荣誉资质', 8, 9, 50, 1264586270),
(6, 3, '资料下载', 6, 7, 50, 1264586295),
(7, 1, '人才招聘', 4, 5, 50, 1264586314),
(8, 1, '留言板', 2, 3, 50, 1264586884),
(9, 1, '总裁', 15, 16, 50, 1267771951);
/**
* 显示树,把所有的节点都显示出来。
* 1、先得到根结点的左右值（默认根节点的title为“顶级目录”）。
* 2、查询左右值在根节点的左右值范围内的记录，并且根据左值排序。
* 3、如果本次记录右值大于前次记录的右值则为子分类，输出时候加空格。
* @return array
**/
function display_tree(){
//获得root左边和右边的值
$arr_lr = $this->category->where("title = '顶级栏目'")->find();
//print_r($arr_lr);
if($arr_lr){
$right = array();
$arr_tree = $this->category->query("SELECT id, type, title, rgt FROM category WHERE lft >= ". $arr_lr['lft'] ." AND lft <=".$arr_lr['rgt']." ORDER BY lft");
foreach($arr_tree as $v){
if(count($right)){
while ($right[count($right) -1] < $v['rgt']){
array_pop($right);
}
}
$title = $v['title'];
if(count($right)){
$title = '|-'.$title;
}
$arr_list[] = array('id' => $v['id'], 'type' => $type, 'title' => str_repeat(' ', count($right)).$title, 'name' =>$v['title']);
$right[] = $v['rgt'];
}
return $arr_list;
}
}
好了 只要这样所有的分类都可以一次性查询出来了，而不用通过递归了。
下面的问题是怎样进行插入、删除和修改操作
插入:插入操作很简单找到其父节点，之后把左值和右值大于父节点左值的节点的左右值加上2，之后再插入本节点，左右值分别为父节点左值加一和加二，可以用一个存储过程来操作：
CREATE PROCEDURE `category_insert_by_parent`(IN pid INT,IN title VARCHAR(20), IN type INT, IN l_order INT, IN pubtime INT)
BEGIN
DECLARE myLeft INT;
SELECT lft into myLeft FROM category WHERE id= pid;
UPDATE qy_category SET rgt = rgt + 2 WHERE rgt > myLeft;
UPDATE qy_category SET lft = lft + 2 WHERE lft > myLeft;
INSERT INTO qy_category(type, title, lft, rgt, lorder, create_time) VALUES(type ,title, myLeft + 1, myLeft + 2, l_order, pubtime);
commit;
END
删除操作：
删除的原理：1.得到要删除节点的左右值，并得到他们的差再加一，@mywidth = @rgt - @lft + 1;
2.删除左右值在本节点之间的节点
3.修改条件为大于本节点右值的所有节点，操作为把他们的左右值都减去@mywidth
存储过程如下：
CREATE PROCEDURE `category_delete_by_key`(IN id INT)
BEGIN
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM category
WHERE id = id;
DELETE FROM category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
修改：
要命的修改操作，本人看了很久也没有看出什么规律出来，只要出此下策，先删除再插入，只要调用上面2个存储过程就可以了！
总结：查询方便，但是增删改操作有点繁琐，但是一般分类此类操作不是很多，还是查询用的多，再说弄个存储过程也方便！
上面第三种方案具体讲解类容是从http://home.phpchina.com/space.php?uid=45095&do=blog&id=184675拷贝过来，方便以后自己查看。 暂时从各方面及理论上考虑 偏向于第三方案。不过还没有做过测试，到底效率怎么样。
期待更好的解决方案！
好文要顶 关注我 收藏该文 
+加关注
« 上一篇：mysql命令之二：查看mysql版本的四种方法
» 下一篇：mysql索引之三：索引使用注意规则（索引失效--存在索引但不使用索引）*
1747
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
