MySQL实现类似Oracle中start with功能

作者：Guff_9hys | 2024-07-25 15:35:11

踩

其他的两个链接 http://blog.csdn.net/wzy0623/article/details/50703686

MySQL树形遍历

http://blog.csdn.net/wzy0623/article/details/53924307

MySQL实现树的遍历

什么是父根就是父获取父节点就是获取根肯定得到的值越来越小

SELECT BMBH,CC,SSBMBH FROM ZC_BM
START WITH BMBH=p_bmbh
CONNECT BY PRIOR SSBMBH=BMBH

这个函数是从下往上（根）的，所以是获取父节点查出来的都比传入进去的小也就是调用 getParentList

-- 获取父节点树：
DROP FUNCTION IF EXISTS `getParentList`;
DELIMITER $$
CREATE FUNCTION `getParentList`(rootId VARCHAR(10000)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
DECLARE sParentList VARCHAR(10000);
DECLARE sParentTemp VARCHAR(10000);
SET sParentTemp =rootId;
WHILE sParentTemp IS NOT NULL DO
IF (sParentList IS NOT NULL) THEN
SET sParentList = CONCAT(sParentTemp,',',sParentList);
ELSE
SET sParentList = CONCAT(sParentTemp);
END IF;
SELECT GROUP_CONCAT(pid) INTO sParentTemp FROM FBS_SYS_ORG WHERE FIND_IN_SET(id,sParentTemp)>0;
END WHILE;
RETURN sParentList;
END$$
DELIMITER ;
-- 调用
SELECT * FROM FBS_SYS_ORG WHERE FIND_IN_SET(id, getParentList('31d8633768a9459d98c9ced15d528cd0'));
----------------------------------------------------------------------------------------------------------------------
-- 获取子节点树
DROP FUNCTION IF EXISTS `getChildList`;
DELIMITER $$
CREATE FUNCTION `getChildList`(rootId VARCHAR(100)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
DECLARE sChildList VARCHAR(10000);
DECLARE sChildTemp VARCHAR(1000);
SET sChildTemp =rootId;
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
SET sChildList = CONCAT(sChildTemp,',',sChildList);
ELSE
SET sChildList = CONCAT(sChildTemp);
END IF;
SELECT GROUP_CONCAT(id) INTO sChildTemp FROM fbs_sys_org WHERE FIND_IN_SET(pid, sChildTemp)>0;
END WHILE;
RETURN sChildList;
END$$
DELIMITER ;
--调用
SELECT * FROM FBS_SYS_ORG WHERE FIND_IN_SET(pid, getChildList('2a597432c97e4111856213ed52b59e64'));
----------------------------------------------------------------------------------------------------------------------
-- 查询两者之间的节点数
DROP FUNCTION IF EXISTS `getBetweenNodes`;
DELIMITER $$
CREATE FUNCTION `getBetweenNodes`(startNodeId VARCHAR(100), endNodeId VARCHAR(100)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
DECLARE sBetweenNodes VARCHAR(10000);
SELECT GROUP_CONCAT(id) INTO sBetweenNodes FROM FBS_SYS_ORG WHERE FIND_IN_SET(id, getParentNodes(endNodeId)) AND FIND_IN_SET(pid, getChildNodes(startNodeId));
RETURN sBetweenNodes;
END$$
DELIMITER ;
--调用
SELECT * FROM FBS_SYS_ORG WHERE FIND_IN_SET(id, getBetweenNodes('2a597432c97e4111856213ed52b59e64', '31d8633768a9459d98c9ced15d528cd0'));
--或者不定义该函数而直接用sql调两次：
SELECT * FROM FBS_SYS_ORG WHERE FIND_IN_SET(id, getParentNodes('31d8633768a9459d98c9ced15d528cd0')) AND FIND_IN_SET(pid, getChildNodes('2a597432c97e4111856213ed52b59e64'));
----------------------------------------------------------------------------------------------------------------------
--若没有定义getParentNodes()函数和getChildNodes()函数，则如下实现：
DROP FUNCTION IF EXISTS `getBetweenNodes`;
DELIMITER $$
CREATE FUNCTION `getBetweenNodes`(startNodeId VARCHAR(100), endNodeId VARCHAR(100)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
DECLARE sBetweenNodes VARCHAR(10000);
DECLARE sParentNodes VARCHAR(10000);
DECLARE sParentNodeTemp VARCHAR(10000);
DECLARE sChildNodes VARCHAR(10000);
DECLARE sChildNodeTemp VARCHAR(10000);
SET sChildNodeTemp =startNodeId;
WHILE sChildNodeTemp IS NOT NULL DO
IF (sChildNodes IS NOT NULL) THEN
SET sChildNodes = CONCAT(sChildNodeTemp,',',sChildNodes);
ELSE
SET sChildNodes = CONCAT(sChildNodeTemp);
END IF;
SELECT GROUP_CONCAT(id) INTO sChildNodeTemp FROM fbs_sys_org WHERE FIND_IN_SET(pid, sChildNodeTemp)>0;
END WHILE;
SET sParentNodeTemp =endNodeId;
WHILE sParentNodeTemp IS NOT NULL DO
IF (sParentNodes IS NOT NULL) THEN
SET sParentNodes = CONCAT(sParentNodeTemp,',',sParentNodes);
ELSE
SET sParentNodes = CONCAT(sParentNodeTemp);
END IF;
SELECT GROUP_CONCAT(pid) INTO sParentNodeTemp FROM FBS_SYS_ORG WHERE FIND_IN_SET(id, sParentNodeTemp)>0;
END WHILE;
SELECT GROUP_CONCAT(id) INTO sBetweenNodes FROM fbs_sys_org WHERE FIND_IN_SET(id, sParentNodes) AND FIND_IN_SET(pid, sChildNodes);
RETURN sBetweenNodes;
END$$
DELIMITER ;

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Guff_9hys/article/detail/880921