mysql查询所有子集_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 递归查询 (函数)

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

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