表结构
treeNodes
id | parentid | … |
---|---|---|
… |
查父集合
--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
查子集合
--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
原文地址:https://www.cnblogs.com/shi2310/p/7508765.html