- -根据输入的组织机构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;