MySQL 树节点递归遍历所以子节点

DELIMITER $$
DROP FUNCTION IF EXISTS `getChildList`$$
CREATE FUNCTION `getChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 READS SQL DATA
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(`deptId`) INTO `sTempChd` FROM dept WHERE FIND_IN_SET(`parentId`,`sTempChd`)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;

SELECT getChildList(1);

　　

 

转载于:https://www.cnblogs.com/zhangjinru123/p/9724219.html