建函数
一、查父集合
--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中进行树状所有子节点的查询 (函数,存储过程)