mysql实现查询所有子节点_MySQL实现树状所有子节点查询的方法以及所有父节点查询的方法...

递归获取当前节点下面的所有子节点数据

mysql 添加一个函数  getChild

CREATE DEFINER=`root`@`localhost` FUNCTION `getChild`(rootId INT) RETURNS varchar(1000) CHARSET utf8

BEGIN

DECLARE ptemp VARCHAR(1000);

DECLARE ctemp VARCHAR(1000);

SET ptemp = '#';

SET ctemp =CAST(rootId AS CHAR);

WHILE ctemp IS NOT NULL DO

SET ptemp = CONCAT(ptemp,',',ctemp);

SELECT GROUP_CONCAT(id) INTO ctemp FROM fht_ams_dept

WHERE FIND_IN_SET(pid,ctemp)>0;

END WHILE;

RETURN ptemp;

END

@Query(value = " select id from fht_ams_dept where find_in_set(id,getChild(?1))" ,nativeQuery = true)

ListfindAllDeptIdChild(Long pid);

递归获取当前节点上面的所有父节点数据

mysql 添加一个函数

CREATE DEFINER=`root`@`%` FUNCTION `getParList`(rootId INT) RETURNS varchar(1000) CHARSET utf8mb4

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempPar VARCHAR(1000);

SET sTemp = '';

SET sTempPar =rootId;

WHILE sTempPar is not null DO

IF sTemp != '' THEN

SET sTemp = concat(sTemp,',',sTempPar);

ELSE

SET sTemp = sTempPar;

END IF;

SET sTemp = concat(sTemp,',',sTempPar);

SELECT group_concat(pid) INTO sTempPar FROM fht_ams_dept where pid<>id and FIND_IN_SET(id,sTempPar)>0;

END WHILE;

RETURN sTemp;

END

@Query(value = " select id from fht_ams_dept where status=1 and find_in_set(id,getParList(?1))" ,nativeQuery = true)

ListfindAllDeptIdParent(Long id);