现在项目要实现 查询到 某一个人的所有下属人
数据库表包括有
推荐人字段: 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
网上看了很多都没看懂 - -! 求这个存储过程用来查询到 某一个人的所有下属人。。日后好好研究~
参考版主里面的:
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
MySQL中进行树状所有子节点的查询
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>
-> 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>