mysql递归查询，查父集合，查子集合

建函数

一、查父集合

--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'))