一个树sql,不知道如何写(正在考虑用存储过程)
a表 2个字段(id, parentid)
id, parentid
1 0
2 1
3 2
4 1
5 2先在我需要得到以下的结果
1 0
2 1
3 2
5 2
4 1
.。请问如何做(左子树遍历完 再继续左子树 右子树遍历)
a表 2个字段(id, parentid)
id, parentid
1 0
2 1
3 2
4 1
5 2先在我需要得到以下的结果
1 0
2 1
3 2
5 2
4 1
.。请问如何做(左子树遍历完 再继续左子树 右子树遍历)
解决方案 »
- mysql主从服务器切换的问题
- 请教,在安装mysql 选择编码格式的时候 选择啥才能避免中文乱码?
- 求MYSQL 截取字符串语句
- 谁可以帮解释下 key_buffer_size 设置为什么要满足1:1000呢?
- Mysql的主机名问题
- [非常急]据说是新手一劫,mysql用latin1编码,中文查询为什么都是乱码?
- PgManager连接postgresql 提示: cannot load dynamic library libpq.dll
- 在线等,请高手们多多指教!!phpmyadmin配置问题
- 一个在sql server 中很简单的问题,怎么在mysql数据库中直接插入一条记录呀?
- MariaDB 排序存在问题
- mysql 字符变量默认设置(windows)
- 链接MySQL读取数据,中文都变成乱码了?
`id` int(10) NOT NULL,
`fid` int(10) NOT NULL );create table if not exists tmp_table(id bigint(20),fid bigint(20),lvl int)//2 CREATE PROCEDURE useCursor(iid bigint(20),lvl int)
BEGIN
declare tid bigint(20) default -1 ;
declare tfid bigint(20) default -1 ;
declare cur1 CURSOR FOR select id,fid from tree where fid=iid ;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null,tfid=null;
SET @@max_sp_recursion_depth = 10;
OPEN cur1;
FETCH cur1 INTO tid,tfid;
WHILE ( tid is not null )
DO
insert into tmp_table values(tid,tfid,lvl);
call useCursor(tid,lvl+1);
FETCH cur1 INTO tid,tfid ;
END WHILE;
END;
DELIMITER ;3test:
delete from tmp_table ;
call useCursor(0,0);
select * from tmp_table ;
mysql 树形结构查询(存储过程)
减小字体 增大字体
就用数据数据库表地址数据(中国地区) 来说吧(用Windows 请使用 gbk !!) 可直接运行(去除注解) 存储过程:DELIMITER//
dropprocedureifexists findLChild//
/*iid递归父节点,layer允许递归深度*/
CREATEPROCEDUREfindLChild(iidbigint(20),layerbigint(20))
BEGIN
/*创建接受查询的临时表*/
createtemporary tableifnotexiststmp_table(idbigint(20),namevarchar(50))ENGINE=InnoDBDEFAULTCHARSET=utf8;
/*最高允许递归数*/
SET@@max_sp_recursion_depth=99;
calliterative(iid,layer);/*核心数据收集*/
select*fromtmp_table;/*展现*/
droptemporary tableif exists tmp_table;/*删除临时表*/
END;//
DELIMITER;
DELIMITER//
dropprocedureifexists iterative//
CREATEPROCEDUREiterative(iidbigint(20),layerbigint(20))
BEGIN
declaretidbigint(20)default-1;
declaretnamevarchar(50)charactersetutf8;
/*游标定义*/
declarecur1CURSORFORselectid,namefromlocationwherefid=iid;
declareCONTINUEHANDLERFORSQLSTATE'02000'SETtid=null;
/*允许递归深度*/
iflayer>0then
OPENcur1;
FETCHcur1INTOtid,tname;
WHILE(tidisnotnull)
DO
/*核心数据收集*/
insertintotmp_tablevalues(tid,tname);
calliterative(tid,layer-1);
FETCHcur1INTOtid,tname;
ENDWHILE;
endif;
END;//
DELIMITER; //运行!!mysql> call findLChild(1,1);
+------+------------------+
| id | name |
+------+------------------+
| 2 | 北京 |
| 4 | 上海 |
| 6 | 香港特别行政区 |
| 8 | 澳门特别行政区 |
| 10 | 河北 |
| 23 | 山西 |
| 35 | 辽宁 |
| 50 | 吉林 |
| 60 | 黑龙江 |
| 74 | 江苏 |
| 88 | 浙江 |
| 101 | 安徽 |
| 119 | 福建 |
| 129 | 江西 |
| 142 | 山东 |
| 160 | 河南 |
| 179 | 湖北 |
| 198 | 湖南 |
| 213 | 广东 |
| 235 | 甘肃 |
| 250 | 四川 |
| 272 | 贵州 |
| 282 | 海南 |
| 301 | 云南 |
| 318 | 青海 |
| 327 | 陕西 |
| 348 | 广西壮族自治区 |
| 363 | 西藏自治区 |
| 371 | 宁夏回族自治区 |
| 377 | 新疆维吾尔自治区 |
| 400 | 内蒙古自治区 |
| 413 | 台湾省 |
+------+------------------+
32 rows in set (0.02 sec)
MySQL中进行树状所有子节点的查询
样例数据:
mysql> create table treeNodes
-> (
-> id int primary key,
-> nodename varchar(20),
-> pid int
-> );
Query OK, 0 rows affected (0.09 sec)
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, 得到一个由所有子节点号组成的字符串.
mysql> delimiter //
mysql>
mysql> CREATE FUNCTION `getChildLst`(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
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
使用我们直接利用find_in_set函数配合这个getChildlst来查找
mysql> select getChildLst(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, getChildLst(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, getChildLst(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为调用入口过程,准备临时表及初始化。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的限制。
方法三:利用中间表和过程(本方法由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;
END;
//delimiter ;CALL showTreeNodes_yongyupost2000(0);执行完后会产生一个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中用存储过程比较简单的实现方法。本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
(比如 用动态sql语句 来实现)??