mysql 祖祖辈辈、子子孙孙（上级，下级）递归查询

子子孙孙：

CREATE DEFINER=``@`` FUNCTION `getChildPostList`(`userId` int(11),`projectId` varchar(32)) RETURNS varchar(1000) CHARSET latin1
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
DECLARE res VARCHAR(1000);
DECLARE project VARCHAR(32);
DECLARE selfPost VARCHAR(32);

SET sTemp = '';
SET project = projectId ;
SET sTempChd =(select GROUP_CONCAT(post_id) from tb_post_user where user_id=userId);
set selfPost= sTempChd;
IF project is not null then
WHILE sTempChd is not null DO

SELECT group_concat(post_id) INTO sTempChd FROM tb_team_post t
join tb_project_team t1 on t.team_id=t1.team_id
where t1.project_id=project and FIND_IN_SET(parent_id,sTempChd)>0;
IF sTempChd is not null then
if sTemp = '' then
SET sTemp =sTempChd;
else
SET sTemp = concat(sTemp,',',sTempChd);
end if ;
end if ;


END WHILE;

ELSE
WHILE sTempChd is not null DO
SELECT group_concat(post_id) INTO sTempChd FROM tb_team_post t where FIND_IN_SET(parent_id,sTempChd)>0;
IF sTempChd is not null then
if sTemp = '' then
SET sTemp =sTempChd;
else
SET sTemp = concat(sTemp,',',sTempChd);
end if ;
end if ;

END WHILE;
END IF;


RETURN sTemp;
END

祖祖辈辈：

SELECT GROUP_CONCAT(T2.post_name ORDER BY T2.create_time)
FROM (
SELECT
@r AS _post_id,
(SELECT @r := parent_id FROM tb_team_post WHERE post_id = _post_id) AS parent_id
FROM
(SELECT @r := #{postId}, @l := 0) vars,
tb_team_post h
WHERE @r <>-1) T1
JOIN tb_team_post T2
ON T1._post_id = T2.post_id