MySQL实现类似Oracle中start with功能

-- 获取父节点树：DROP FUNCTION IF EXISTS `getParentList`;DELIMITER $$CREATE FUNCTION `getParentList`(rootId VARCHAR(10000)) RETURNS VARCHAR(10000) CHARSET utf8BEGIN 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 utf8BEGIN   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 utf8BEGIN   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 utf8BEGIN   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 ;

参考链接：http://www.jb51.net/article/48407.htm