MySQL实现类似Oracle中start with功能

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

 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 ;