现在项目要实现 查询到 某一个人的所有下属人
数据库表包括有
推荐人字段: igtxtRecommendCardNoTxt 用户ID: id
假如 我推荐了 A , A推荐了B , B推荐了C 。 则我的下属人中就有 A,B,C
A的下属人中就有 B,C
网上看了很多都没看懂 - -! 求这个存储过程用来查询到 某一个人的所有下属人。。日后好好研究~
数据库表包括有
推荐人字段: igtxtRecommendCardNoTxt 用户ID: id
假如 我推荐了 A , A推荐了B , B推荐了C 。 则我的下属人中就有 A,B,C
A的下属人中就有 B,C
网上看了很多都没看懂 - -! 求这个存储过程用来查询到 某一个人的所有下属人。。日后好好研究~
MySQL中进行树状所有子节点的查询
mysql> select * from tb_tree_example;
-> //
+----+----------+-----------+
| id | username | introduer |
+----+----------+-----------+
| 1 | A | I |
| 2 | B | A |
| 3 | B2 | A |
| 4 | C | B |
| 5 | C1 | B2 |
| 6 | D | C |
| 7 | D2 | C |
+----+----------+-----------+
7 rows in set (0.00 sec)mysql> DROP PROCEDURE IF EXISTS sp_get_treedata;
-> CREATE PROCEDURE sp_get_treedata
-> (
-> IN i_First_Introduer varchar(20),
-> OUT o_Result varchar(2000)
-> )
-> BEGIN
-> declare v_level int;
-> declare v_flag tinyint;
-> drop TABLE IF EXISTS tb_get_treeuser_tmp;
-> CREATE TABLE tb_get_treeuser_tmp
-> (
-> id int auto_increment primary key,
-> Tree_Level int,
-> UserName varchar(20),
-> Introduer varchar(20)
-> );
->
-> Set v_level = 1;
-> SET v_flag=0;
->
-> INSERT into tb_get_treeuser_tmp(Tree_Level,UserName,Introduer)
-> SELECT v_level,UserName,Introduer FROM tb_tree_example WHERE Int
roduer=i_First_Introduer;
->
-> REPEAT
-> INSERT into tb_get_treeuser_tmp(Tree_Level,UserName,Introduer)
-> SELECT v_level+1,a.UserName,a.Introduer
-> FROM tb_tree_example a, tb_get_treeuser_tmp b
-> WHERE a.Introduer=b.UserName and b.Tree_
Level=v_level;
-> SET v_flag = FOUND_ROWS();
-> SET v_level = v_level + 1;
-> UNTIL (v_flag=0) END REPEAT;
->
-> SET @str='';
-> SELECT @str := concat(@str,UserName,',') FROM tb_get_treeuser_tmp;
-> SET @str=left(@str,char_length(@str)-1);
-> SET o_Result = @str;
-> DROP TABLE IF EXISTS tb_get_treeuser_tmp;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> CALL sp_get_treedata('i', @A);
-> SELECT @A;
-> //
+-----------------------------------+
| @str := concat(@str,UserName,',') |
+-----------------------------------+
| A, |
| A,B, |
| A,B,B2, |
| A,B,B2,C, |
| A,B,B2,C,C1, |
| A,B,B2,C,C1,D, |
| A,B,B2,C,C1,D,D2, |
+-----------------------------------+
7 rows in set (0.20 sec)Query OK, 0 rows affected, 1 warning (0.22 sec)+------------------+
| @A |
+------------------+
| A,B,B2,C,C1,D,D2 |
+------------------+
1 row in set (0.22 sec)mysql>
mysql> CALL sp_get_treedata('A', @A);
-> SELECT @A;
-> //
+-----------------------------------+
| @str := concat(@str,UserName,',') |
+-----------------------------------+
| B, |
| B,B2, |
| B,B2,C, |
| B,B2,C,C1, |
| B,B2,C,C1,D, |
| B,B2,C,C1,D,D2, |
+-----------------------------------+
6 rows in set (0.17 sec)Query OK, 0 rows affected, 1 warning (0.19 sec)+----------------+
| @A |
+----------------+
| B,B2,C,C1,D,D2 |
+----------------+
1 row in set (0.19 sec)mysql>
看来你看得很不仔细啊!另外是存储过程,不是函数(函数的确不能递归)MySQL中你可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。如下例设为12.
mysql> set max_sp_recursion_depth=12;
Query OK, 0 rows affected (0.00 sec)
你好 谢谢你的回复, 我在运用你上述提供的例子的方法. 出现报错 "Recursive stored routines are not allowed."DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`showChildLst`$$CREATE PROCEDURE `showChildLst`(IN rootId INT)
BEGIN
CREATE TABLE IF NOT EXISTS tmpLst (sno int primary key auto_increment,id varchar(20));
DELETE FROM tmpLst;
CALL createChildLst(rootId);
select * from tmpLst;
END$$DELIMITER ;
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`createChildLst`$$CREATE PROCEDURE `createChildLst`(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT igtxtNickNameTxt FROM user where igtxtRecommendCardNoTxt=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 ;请帮我看看.谢谢
createChildLst();
不是循环的调用 createChildLst();本身吗? 出现的问题就是showChildLst 调用createChildLst(); 没有错
如果 createChildLst();调用自己就报错了.