MYSQL 递归树查询的实现

​    在 oracle 中我们可以使用 connect by prior 函数来实现组织树表递归的查询，但是在 mysql 中却没有该方法，为了能够使用该方法我们就得自己创建函数来实现该组织树的递归查询。以下实现了 2 种方式来满足子节点的递归查询与父节点的递归查询。

实现子递归查询

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

oracle 中使用示例代码：

SELECT
	T.ID
FROM
	bas_basic_tree T CONNECT BY PRIOR T.ID = T.PID
	START WITH T.ID = #{id}

mysql 中使用示例代码：

SELECT
	ID
FROM 
    bas_basic_tree T,
    (SELECT @DATAS := getChildId_bas_basic_tree (#{id})) x 
WHERE find_in_set (ID, @DATAS)

mysql 方法的实现：

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

drop function if exists getChildId_bas_basic_tree;

DELIMITER //
create function getChildId_bas_basic_tree(rootId varchar(20)) 
returns varchar (1000)
BEGIN 
    DECLARE pTemp VARCHAR(1000);  			 
    DECLARE cTemp VARCHAR(1000);      -- 节点ID(临时变量)

    SET pTemp = '$';  
    SET cTemp =cast(rootId as CHAR);  -- 把rootId强制转换为字符。

    WHILE cTemp is not null DO  
    	SET pTemp = concat(pTemp,',',cTemp);  -- 把所有节点连接成字符串。
    	SELECT group_concat(id) INTO cTemp FROM bas_basic_tree   
    	WHERE FIND_IN_SET(pid,cTemp)>0;
    END WHILE;  
    RETURN pTemp;  
END //
DELIMITER ;

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

oracle 中使用示例代码：

SELECT
	T.ID
FROM
	bas_basic_tree T CONNECT BY PRIOR T.ID = T.PID
	START WITH T.ID in( #{ids} ) 
	
-- 其中ids比如是 ('1', '2') 这种

mysql 中使用示例代码：

SELECT
	*
FROM
	bas_basic_tree T ,
	(select @DATAK := 
		(	
			SELECT
			    -- 使用该方法把所有的ID用逗号分割连接成一个字符串
				group_concat(T.ID)
			FROM
				bas_basic_tree T
			WHERE
			NAME LIKE #{orgname}
			-- 括号里面查询出来的结果类似与 '1,2' 这种
		)
	) z,
	(SELECT @DATAS := getParentId_bas_basic_tree_more (@DATAK) ) x 
WHERE find_in_set (T.ID, @DATAS)

-- 注意这里select @DATAK 与 SELECT @DATAS 顺序不能乱，否则会出现数据错误

mysql 方法的实现：

drop function if exists getChildId_bas_basic_tree_more;

DELIMITER //
create function getChildId_bas_basic_tree_more(nodes varchar(1000)) 
returns varchar (5000)

BEGIN 
       DECLARE pTemp VARCHAR(1000);  			 
       DECLARE cTemp VARCHAR(5000);  
	   
	   SET @array_content= nodes; 
       SET @i=1;
       SET @count=CHAR_LENGTH(@array_content) -CHAR_LENGTH(REPLACE(@array_content,',','')) + 1;
	   SET pTemp = '$';  

       WHILE @i <= @count DO
		   SET cTemp = SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,',',@i),',',-1);
		   WHILE cTemp is not null DO  
			 SET pTemp = concat(pTemp,',',cTemp);  
			 SELECT group_concat(id) INTO cTemp FROM bas_basic_tree   
			 WHERE FIND_IN_SET(pid,cTemp) > 0;
		   END WHILE;  
		   SET @i = @i + 1; 
	   END WHILE; 
      
       RETURN pTemp;  

END //
DELIMITER ;

实现父递归查询

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 ;