源文地址:https://www.cnblogs.com/shi2310/p/7508765.html

查父集合

-- drop FUNCTION `getParentList`  CREATE FUNCTION `getParentList`(rootId varchar(100))   RETURNS varchar(1000)   BEGIN   DECLARE fid varchar(100) default '';   DECLARE str varchar(1000) default rootId;     WHILE rootId is not null  do       SET fid =(SELECT parentid FROM treeNodes WHERE id = rootId);       IF fid is not null THEN           SET str = concat(str, ',', fid);           SET rootId = fid;       ELSE           SET rootId = fid;       END IF;   END WHILE;   return str;  END

查询语句

select getParentList('001001001001001');     select * from sbkfwh where FIND_IN_SET(id,getParentList('001001001001002'))

查子集合

-- drop FUNCTION `getChildList`  CREATE FUNCTION `getChildList`(rootId varchar(100))   RETURNS varchar(2000)  BEGIN   DECLARE str varchar(2000);  DECLARE cid varchar(100);   SET str = '$';   SET cid = rootId;   WHILE cid is not null DO       SET str = concat(str, ',', cid);       SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(parentid, cid) > 0;   END WHILE;   RETURN str;   END

执行语句

select getParentList('001001001');   select * from sbkfwh where FIND_IN_SET(id,getChildList('001001001'))