mysql 获取 父节点_mysql获取子父级节点

获取所有子节点

DROP FUNCTION IF EXISTS `F_Co29_GetAllChildrenIdsOfTaskevent`;

DELIMITER //

CREATE FUNCTION `F_Co29_GetAllChildrenIdsOfTaskevent`(

vId BIGINT

)

RETURNS VARCHAR(5000)

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempChd VARCHAR(5000);

DECLARE vCount INT;

SET sTemp = '';

SET sTempChd = CAST(vId AS CHAR);

SET vCount = 1000;

WHILE sTempChd IS NOT NULL AND vCount > 0 DO

SET vCount = vCount - 1;

IF LENGTH(sTemp) > 0 THEN

SET sTemp = CONCAT(sTemp,',',sTempChd);

ELSE

SET sTemp = sTempChd;

END IF;

SELECT GROUP_CONCAT(`Id`) INTO sTempChd FROM `taskevent` WHERE FIND_IN_SET(`PID`,sTempChd)>0;

END WHILE;

RETURN sTemp;

END//

DELIMITER ;

获取所有父级节点

DROP FUNCTION IF EXISTS `F_Co30_GetAllParentIdsOfTaskevent`;

DELIMITER //

CREATE FUNCTION `F_Co30_GetAllParentIdsOfTaskevent`(

vId BIGINT

)

RETURNS VARCHAR(5000)

BEGIN

DECLARE vPId BIGINT;

DECLARE sTemp VARCHAR(1000) DEFAULT '';

WHILE vId IS NOT NULL DO

SELECT `PID` into PId FROM `taskevent` WHERE `Id` = vId;

IF vPId IS NOT NULL THEN

SET sTemp = CONCAT(sTemp, ',', vPId);

SET vId = vPId;

ELSE

SET vId = vPId;

END IF;

END WHILE;

SET sTemp = SUBSTRING(sTemp,2);

RETURN sTemp;

END//

DELIMITER ;