mysql根据父子关系查询sql 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 ;