mysql 函数实现父子查询

DELIMITER //

CREATE FUNCTION get_all_father(rootId INT)
RETURNS varchar(1000) 
DETERMINISTIC
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempPar VARCHAR(1000); 
    SET sTemp = ''; 
    SET sTempPar =rootId; 
 
    #循环递归
    WHILE sTempPar is not null DO 
        #判断是否是第一个，不加的话第一个会为空
        IF sTemp != '' THEN
            SET sTemp = concat(sTemp,',',sTempPar);
        ELSE
            SET sTemp = sTempPar;
        END IF;
        
        SELECT group_concat(FParentID) INTO sTempPar FROM T_Account where FParentID<>PID and FIND_IN_SET(PID,sTempPar)>0; 
    END WHILE; 
 
RETURN sTemp; 
END //
DELIMITER ;


DELIMITER //

CREATE FUNCTION get_all_children(id INT)
RETURNS VARCHAR(2000)
DETERMINISTIC
BEGIN
    DECLARE children VARCHAR(2000);
    DECLARE loop_id INT;
    SET children = id;
    SET loop_id = id;
    WHILE loop_id IS NOT NULL DO
            IF id <> loop_id
            THEN
                SET children = CONCAT(children, ',', (SELECT GROUP_CONCAT(PID) FROM T_Account WHERE PID = loop_id));
            END IF;
      SET loop_id = (SELECT PID FROM T_Account WHERE FParentID = loop_id);
    END WHILE;
    RETURN children;
END //

DELIMITER ;


SELECT get_all_children(1) 

select get_all_father(5);