mysql 递归

曹振华

已于 2023-06-13 14:25:12 修改

阅读量418

点赞数

分类专栏： mysql 文章标签： mysql 数据库递归函数子孙

于 2022-06-29 14:29:58 首次发布

本文链接：https://blog.csdn.net/chadcao/article/details/125521415

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

函数

DROP FUNCTION IF EXISTS GetChildren;
 
CREATE FUNCTION GetChildren(InputDeptId char(36)) 
RETURNS VARCHAR(4000)
BEGIN

declare deptIds VARCHAR(4000) default '';

select CONCAT(@pids,',',IFNULL(GROUP_CONCAT(DeptId),'$')) into deptIds from (
              select t1.DeptId,
              if(find_in_set(UpperId, @pids) > 0,
       @pids := concat(@pids, ',', DeptId), 0) as ischild
              from (
										 select DeptId,UpperId from MDM_Dept t 
										 where t.STATUS=1
										 order by UpperId, DeptId
                  ) t1,
                  (select @pids := InputDeptId) t2
             ) t3 where ischild != 0 ;
 
return deptIds; 
END;

调用

select * from MDM_Dept 
where FIND_IN_SET(DeptId, GetChildren('b9d22dc7-5651-4e87-a59b-7f11ef178e45'))

select * from 
(
	select GetFullPath(DeptId) fullPath,DeptId,`Name`,UpperId 
	from MDM_Dept 
	where FIND_IN_SET(DeptId, GetChildren('b9d22dc7-5651-4e87-a59b-7f11ef178e45'))
) t order by t.fullPath