mysql多级树形结构数据，随着时间推移下级推荐会员级数会回来越多，如何获取某会员下面的所有会员数量？

表结构（id,用户名，推荐用户名）

1，建立测试表和数据：
DROp TABLE IF EXISTS csdn.channel;
CREATE TABLE csdn.channel (
id INT(11) NOT NULL AUTO_INCREMENT,
cname VARCHAr(200) DEFAULT NULL,
parent_id INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO channel(id,cname,parent_id)
VALUES (13,'首页',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻灯片',13),
(17,'帮忙',14),
(18,'栏目简介',17);
DROP TABLE IF EXISTS channel;

2，利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：

2.1，从某节点向下遍历子节点，递归生成临时表数据
-- pro_cre_childlist
DELIMITER $$
DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist$$
CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERe parent_id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;

INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);

OPEN cur1;

FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_childlist(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;

CLOSE cur1;
END$$

2.2，从某节点向上追溯根节点，递归生成临时表数据
-- pro_cre_parentlist
DELIMITER $$
DROp PROCEDURE IF EXISTS csdn.pro_cre_parentlist$$
CREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERe id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;

INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);

OPEN cur1;

FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_parentlist(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;

CLOSE cur1;
END$$

我。。知。。道 加。。我。。私。。聊

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息