mysql获取子父级节点

最新推荐文章于 2024-09-27 09:59:22 发布

weixin_30767835

最新推荐文章于 2024-09-27 09:59:22 发布

阅读量275

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/vofill/p/10208514.html

获取所有子节点

　　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 ;