mysql递归函数的应用

• -根据输入的组织机构ID，查询出该组织机构的下属组织机构ID（不包含输入的组织机构）
• mysql函数不能返回结果集，只能将结果集拼接成一个字符串返回,如:2,3,4

DROP FUNCTION IF EXISTS SearchSubOrgByID_FUN;

CREATE FUNCTION SearchSubOrgByID_FUN (
_ID int(11)
)

RETURNS varchar(512)

BEGIN
return (
select GROUP_CONCAT(cast(id as char(11)) SEPARATOR ‘,’) as ids from (
select
t1.id,t1.ParentID,
if(find_in_set(ParentID, @pids) > 0, @pids := concat(@pids, ‘,’, id), 0) as ischild
from (select id,ParentID from organization t where t.IsEnable = 1 order by ParentID, id) t1,
(select @pids := _ID) t2
) t3 where ischild != 0
);
END;