查父集合

1--drop FUNCTION `getParentList`  
2CREATE FUNCTION `getParentList`(rootId varchar(100))   
3RETURNS varchar(1000)   
4BEGIN   
5DECLARE fid varchar(100) default '';   
6DECLARE str varchar(1000) default rootId;   
7  
8WHILE rootId is not null  do   
9    SET fid =(SELECT parentid FROM treeNodes WHERE id = rootId);   
10    IF fid is not null THEN   
11        SET str = concat(str, ',', fid);   
12        SET rootId = fid;   
13    ELSE   
14        SET rootId = fid;   
15    END IF;   
16END WHILE;   
17return str;  
18END

查询语句

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

查子集合

1--drop FUNCTION `getChildList`  
2CREATE FUNCTION `getChildList`(rootId varchar(100))   
3RETURNS varchar(2000)  
4BEGIN   
5DECLARE str varchar(2000);  
6DECLARE cid varchar(100);   
7SET str = '$';   
8SET cid = rootId;   
9WHILE cid is not null DO   
10    SET str = concat(str, ',', cid);   
11    SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(parentid, cid) > 0;   
12END WHILE;   
13RETURN str;   
14END

查询语句

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

转载https://www.cnblogs.com/feifeicui/p/9576881.html