Mysql递归函数，MySQL树形结构查询返回

兰舟轻帆

已于 2024-04-17 16:45:59 修改

阅读量2.7k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle/Mysql/SQL Server 文章标签： MySQL SQL树形结构 SQL树查询 SQL递归

于 2019-07-05 09:41:13 首次发布

本文链接：https://blog.youkuaiyun.com/bj_chengrong/article/details/94717186

Oracle/Mysql/SQL Server 专栏收录该内容

21 篇文章

订阅专栏

1、数据准备


CREATE TABLE `NODE_LIST` (
    `ID` INT (11),
    `NODE_CONTENT` VARCHAR (300),
    `P_ID` INT (11)
); 
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('1','A',NULL);
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('2','B','1');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('3','C','1');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('4','D','2');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('5','E','3');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('6','F','3');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('7','G','5');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('8','H','7');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('9','I','8');
INSERT INTO `NODE_LIST` (`ID`, `NODE_CONTENT`, `P_ID`) VALUES('10','J','8');

2、函数创建


DROP FUNCTION IF EXISTS GET_CHILD_FUN;
CREATE FUNCTION GET_CHILD_FUN(ROOT_ID INT)
	RETURNS VARCHAR(1000)
	BEGIN
       DECLARE P_TEMP VARCHAR(1000);
       DECLARE C_TEMP VARCHAR(1000);
			 SET P_TEMP = '#';
			 SET C_TEMP =CAST(ROOT_ID AS CHAR);
			 WHILE C_TEMP IS NOT NULL DO
					SET P_TEMP = CONCAT(P_TEMP,',',C_TEMP);
					SELECT GROUP_CONCAT(ID) INTO C_TEMP FROM NODE_LIST   
					WHERE FIND_IN_SET(P_ID,C_TEMP)>0; 
			 END WHILE;  
			 RETURN P_TEMP;  
END;

3、数据查询（根据父ID可以查询到所有子ID数据）

SELECT * FROM NODE_LIST WHERE FIND_IN_SET(id, GET_CHILD_FUN(3));