mysql start with_MySQL实现类似Oracle中start with功能

-- 获取父节点树：

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 ;参考链接：

http://www.jb51.net/article/48407.htm