mysql递归查询语句_MYSQL 递归树查询的实现

实现父递归查询

1.单个父递归查询的实现

Oracle 中使用示例代码：

SELECT

T.ID

FROM

bas_basic_tree T CONNECT BY PRIOR T.PID = T.ID

START WITH T.ID = #{id}

MySQL 中使用示例代码：

SELECT

ID

FROM

bas_basic_tree T,

(SELECT @LIST := getParentId_bas_basic_tree (#{id})) x

WHERE find_in_set (ID, @LIST)

MySQL 方法的实现：

-- 在navcat中的查询下执行以下命令

drop function if exists getParentId_bas_basic_tree;

DELIMITER //

create function getParentId_bas_basic_tree(rootId varchar(20))

returns varchar (1000)

BEGIN

DECLARE fid varchar(100) default '';

DECLARE str varchar(1000) default rootId; -- 节点ID(临时变量)

WHILE rootId is not null do

SET fid =(SELECT pid FROM bas_basic_tree WHERE id = rootId);

IF fid is not null THEN

SET str = concat(str, ',', fid);

SET rootId = fid;

ELSE

SET rootId = fid;

END IF;

END WHILE;

return str;

END //

DELIMITER ;

2.多个父递归查询的实现

Oracle 中使用示例代码：

SELECT

T.ID

FROM

bas_basic_tree T CONNECT BY PRIOR T.PID = T.ID

START WITH T.ID in( #{ids} )

-- 其中ids比如是 ('1', '2') 这种

MySQL 中使用示例代码：

SELECT

T.*

FROM

bas_basic_tree T ,

(select @DATAK :=

(

SELECT

-- 使用该方法把所有的ID用逗号分割连接成一个字符串

group_concat(T.ID)

FROM

bas_basic_tree T

WHERE

NAME LIKE #{orgname}

-- 括号里面查询出来的结果类似与 '1,2' 这种

)

) zz,

(SELECT @DATAS := getParentId_bas_basic_tree_more (@DATAK) ) x

WHERE find_in_set (T.ID, @DATAS)

MySQL 方法的实现：

-- 在navcat中的查询下执行以下命令

drop function if exists getParentId_bas_basic_tree_more;

DELIMITER //

create function getParentId_bas_basic_tree_more(nodes varchar(1000))

returns varchar (5000)

BEGIN

DECLARE pTemp VARCHAR(1000);

DECLARE cTemp VARCHAR(5000);

DECLARE xTemp varchar(100);

SET @array_content= nodes;

SET @i=1;

SET @count=CHAR_LENGTH(@array_content) -CHAR_LENGTH(REPLACE(@array_content,',','')) + 1;

SET pTemp = nodes;

WHILE @i <= @count DO

SET cTemp=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,',',@i),',',-1);

WHILE cTemp is not null DO

SET xTemp = (SELECT pid FROM bas_basic_tree WHERE id = cTemp);

IF xTemp is not null THEN

SET pTemp = concat(pTemp, ',', xTemp);

set cTemp = xTemp;

ELSE

set cTemp = xTemp;

END IF;

END WHILE;

SET @i = @i + 1;

END WHILE;

RETURN pTemp;

END //

DELIMITER ;