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

 

Mysql函数中并不支持动态sql，Dynamic SQL is not allowed in stored function or trigger

要想查多个表的，可以建多个函数，或用以下方法

drop FUNCTION `getChildListTest`   
CREATE FUNCTION `getChildListTest`(tableName varchar(64),rootId varchar(100))    
RETURNS varchar(2000)   
BEGIN    
DECLARE str varchar(2000);   
DECLARE cid varchar(100);    
SET str = '$';    
SET cid = rootId;    

IF tableName = 'tableName1' THEN  
		WHILE cid is not null DO    
				SET str = concat(str, ',', cid);    
				SELECT group_concat(id) INTO cid FROM tableName1 where FIND_IN_SET(parentid, cid) > 0;   
		END WHILE;  
ELSEIF tableName = 'tableName2' THEN  
		WHILE cid is not null DO    
				SET str = concat(str, ',', cid);    
				SELECT group_concat(id) INTO cid FROM tableName2 where FIND_IN_SET(parentid, cid) > 0;   
		END WHILE;  
END IF;  

RETURN str;    
END

 

参考：

mysql 递归查询 （函数）

http://my.oschina.net/cheeryzxh007/blog/299475 （ID为INT型）

比较两种mysql递归tree查询效率-mysql递归tree  （函数，存储过程）

http://jingyan.baidu.com/article/647f01158ee0da7f2148a80b.html

MySQL中进行树状所有子节点的查询  （函数，存储过程）

http://blog.csdn.net/ACMAIN_CHM/article/details/4142971