向下/向下递归查询子/父节点

以下语句数据手动创建函数，每个语句在查询语句界面执行就行，不要在函数界面区创建，函数界面不需要"CREATE FUNCTION getChildList(rid VARCHAR(100)) RETURNS VARCHAR(100)"这两句话

1、向下递归查询子节点

SQL手动创建getChildList函数:

CREATE FUNCTION getChildList(rid VARCHAR(100))   
RETURNS VARCHAR(100)
BEGIN 
	 DECLARE sTemp VARCHAR(100);
	 DECLARE sTempFather VARCHAR(100);

	 SET sTemp = '$';
	 SET sTempFather = rid;

	 WHILE sTempFather is not null DO 
			 SET sTemp = concat(sTemp,',',sTempFather);
			 SELECT group_concat(id) INTO sTempFather FROM department where FIND_IN_SET(parentid,sTempFather)>0; 
	 END WHILE;
 RETURN sTemp;
 END

执行该函数：

SELECT * from department where FIND_IN_SET(id,getChildList(2));

2、向上递归查询父节点

====================================

SQL手动创建getParentList函数:

CREATE FUNCTION getParentList(rid VARCHAR(100))   
RETURNS VARCHAR(100)
BEGIN 
	 DECLARE sTemp VARCHAR(100);
	 DECLARE sTempChild VARCHAR(100);

	 SET sTemp = '$';
	 SET sTempChild = rid;

	 WHILE sTempChild is not null DO 
			 SET sTemp = concat(sTemp,',',sTempChild);
			 SELECT group_concat(parentid) INTO sTempChild FROM department where FIND_IN_SET(id,sTempChild)>0; 
	 END WHILE;
 RETURN sTemp;
 END

执行该函数：

SELECT * from department where FIND_IN_SET(pid,getParentList(2));