树函数

CREATE FUNCTION `getChildLst`(rootId INT)
     RETURNS varchar(1000)
     BEGIN
      DECLARE sTemp VARCHAR(1000);
      DECLARE sTempChd VARCHAR(1000);
    
     SET sTemp = '$';
     SET sTempChd =cast(rootId as CHAR);
    
      WHILE sTempChd is not null DO
      SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
      END WHILE;
      RETURN sTemp;
   END ;

select * from treeNodes
     where FIND_IN_SET(id, getChildLst(0));

select id,pid from treeNodes where id in(
     select id from treeNodes where FIND_IN_SET(id, getChildLst(1))
)