数据库递归查询组织机构向上向下

最新推荐文章于 2024-07-22 03:13:12 发布

efilelims

最新推荐文章于 2024-07-22 03:13:12 发布

阅读量684

点赞数

分类专栏： Database 文章标签： org

本文链接：https://blog.csdn.net/wxh160901/article/details/86648184

版权

Database 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

-------表uum_org------有字段 id ， parent ------------------mysql-------------------------------------------------------

SELECT
   *
FROM
   (
       SELECT
           c.plist
       FROM
           (
               SELECT
                   a.id,

               IF (FIND_IN_SET(a.parent ,@pids) > 0,
                           IF (length(@pids) - length(REPLACE(@pids, a.parent, '')) > 1,
                                           IF (length(@pids) - length(REPLACE(@pids, a.id, '')) > 1 ,@pids ,@pids := concat(@pids, ',', a.id)),
                                           @pids := concat(@pids, ',', a.id)),
                   0) AS 'plist',

                   IF (FIND_IN_SET(a.parent ,@pids) > 0,@pids,0) AS ischild
           FROM
               (
                   SELECT   r.id,
                       r.parent
                   FROM
                       uum_org r
               ) a,
               (SELECT @pids := 1) b *****传入参数pid ，此处传入参数为1***
           ) c
       WHERE
           c.ischild != 0
       ORDER BY
           c.id DESC
       LIMIT 0,1
   ) d

---------------------------------------------------------------------------------------------

-- 自对应查询某个机构的所有子机构

-- --------------------------------oracle------wm_concat字符串缓存区是4000------------------------------------
select wm_concat(id) from UUM_ORG o START WITH o.id=143 CONNECT BY PRIOR o.id = o.parent ; -- 拼接过长了会出错

select * from UUM_ORG o START WITH o.parent = '143' CONNECT BY PRIOR o.id = o.parent ;-- 结果中不包括id=143的数据
select * from UUM_ORG o START WITH o.id = '143' CONNECT BY PRIOR o.id = o.parent ; -- 结果中包括id=143的数据

select * from UUM_ORG o START WITH o.id=1 CONNECT BY o.id = PRIOR o.parent ;

-- --------------------------------mysql------弊端字符串缓存区大小限制------------------------------------

SELECT getChildList(1);

drop FUNCTION if exists getChildList;
CREATE FUNCTION `getChildList`(rootId INT) -- rootId为你要查询的节点。
RETURNS VARCHAR(4000)
BEGIN
DECLARE pTemp VARCHAR(4000);
DECLARE cTemp VARCHAR(4000); -- 两个临时变量

SET pTemp = '$';
SET cTemp =cast(rootId as CHAR); -- 把rootId强制转换为字符。

WHILE cTemp is not null DO
SET pTemp = concat(pTemp,',',cTemp); -- 把所有节点连接成字符串。
SELECT group_concat(id) INTO cTemp FROM uum_org WHERE FIND_IN_SET(parent,cTemp)>0; -- FIND_IN_SET(str,strlist)的方法网上大把不解释。
END WHILE;
RETURN pTemp;
END