oracle,mysql树形结构替换

最新推荐文章于 2022-11-01 20:57:33 发布

洋哥哥db

最新推荐文章于 2022-11-01 20:57:33 发布

阅读量192

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_33674508/article/details/119065890

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

-- 树形结构等同于oracle的select * from tb_tree start with id='3' connect by PRIOR id = pid;
-- 从上往下
-- drop procedure getChildLst;
CREATE procedure getChildLst_s_to_x(rootId INT)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM tb_tree where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
select * from tb_tree where FIND_IN_SET(id, sTemp);
end;
call getChildLst_s_to_x(3)

-- 树形结构等同于oracle的select * from tb_tree start with id='3' connect by id = PRIOR pid;
-- 从下往上
-- drop procedure getChildLst_x_to_s;
CREATE procedure getChildLst_x_to_s(rootId INT)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(pid) INTO sTempChd FROM tb_tree where FIND_IN_SET(id,sTempChd)>0;
END WHILE;
select * from tb_tree where FIND_IN_SET(id, sTemp);
end;
call getChildLst_x_to_s(3)