MYSQL 递归实现

获取所有子节点

BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);


SET sTemp = '$';
SET sTempChd = cast(areaId as char);


WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_user_a where FIND_IN_SET(PARENT_ID,sTempChd)>0;
END WHILE;
return sTemp;
END

直接利用find_in_set函数配合这个getChildlst来查找

获取指定层数的父级节点：

BEGIN
DECLARE n INT;
DECLARE sTempChd VARCHAR(4000);


SET n = 0;
SET sTempChd = cast(areaId as char);


WHILE n < num DO
SELECT group_concat(PARENT_ID) INTO sTempChd FROM t_user_a where FIND_IN_SET(ID,sTempChd)>0;
SET n = n+1;
END WHILE;
return sTempChd;
END

获取指定层数的子节点集合

BEGIN
DECLARE n INT;
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);


SET n = 0;
SET sTemp = '$';
SET sTempChd = cast(a as char);


WHILE sTempChd is not NULL DO
IF n = num THEN
SET sTemp = CONCAT(sTemp,',',sTempChd);
END IF;
IF n <= num THEN
SELECT group_concat(id) INTO sTempChd FROM t_user_a where FIND_IN_SET(PARENT_ID,sTempChd)>0;
ELSE SET sTempChd = NULL;
END IF;
SET n = n+1;
END WHILE;
return sTemp;
END