认真读代码即可看懂,其中 -1为根节点的父级编号。其余多看几遍即可读懂。直接粘贴到navacat 下执行即可。
--获取某个节点所有父节点编号函数?
DROP FUNCTION IF EXISTS `bhpp2.0_evenmngtservice`.`getParentLst`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `bhpp2.0_evenmngtservice`.`getParentLst`(`thingNum` varchar(30)) RETURNS varchar(21845) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21845);
DECLARE sTempParent VARCHAR(30);
DECLARE sTempThingNum VARCHAR(30);
SET sTemp = '^';
SET sTempParent = thingNum;
WHILE sTempParent !="" AND sTempParent !="-1" DO
SET sTemp = concat(sTemp,',',sTempParent);
SET sTempThingNum="";
SELECT beet.parentthingnum INTO sTempThingNum FROM `bhpp2.0_evenmngtservice`.`eventmngt_thing` beet where FIND_IN_SET(beet.thingnum,sTempParent)>0;
SET sTempParent=sTempThingNum;
END WHILE;
RETURN sTemp;
END
;;
DELIMITER ;