mysql 类似 oracle connect by,mysql实现类似oracle的connect by功能

1.生成测试表与数据:create table mytest (  id int primary key,  nodename varchar(20),  pid int );insert into mytest (id,nodename,pid)values(  1 ,'A', 0);insert into mytest (id,nodename,pid)values(  2 ,'B', 1);insert into mytest (id,nodename,pid)values(  3 ,'C', 1);insert into mytest (id,nodename,pid)values(  4 ,'D', 2);insert into mytest (id,nodename,pid)values(  5 ,'E', 2);insert into mytest (id,nodename,pid)values(  6 ,'F', 3);insert into mytest (id,nodename,pid)values(  7 ,'G', 6);insert into mytest (id,nodename,pid)values(  8 ,'H', 0);insert into mytest (id,nodename,pid)values(  9 ,'I', 8);insert into mytest (id,nodename,pid)values( 10 ,'J', 8);insert into mytest (id,nodename,pid)values( 11 ,'K', 8);insert into mytest (id,nodename,pid)values( 12 ,'L', 9);insert into mytest (id,nodename,pid)values( 13 ,'M', 9);insert into mytest (id,nodename,pid)values( 14 ,'N',12);insert into mytest (id,nodename,pid)values( 15 ,'O',12);insert into mytest (id,nodename,pid)values( 16 ,'P',15);insert into mytest (id,nodename,pid)values( 17 ,'Q',15);2.创建函数:delimiter $$CREATE FUNCTION `getParentList` (rootId VARCHAR (50)) RETURNS VARCHAR (1000)  BEGIN  DECLARE sParentList VARCHAR (1000) ;  DECLARE sParentTemp VARCHAR(1000);   SET sParentTemp =CAST(rootId AS CHAR);   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 mytest WHERE FIND_IN_SET(id,sParentTemp)>0;  END WHILE;  RETURN sParentList;  END$$ DELIMITER ; 3.测试验证:set global log_bin_trust_function_creators=1;SELECT id,pid,getParentList(id) FROM mytest;mysql> SELECT id,pid,getParentList(id) FROM mytest;+----+------+-------------------+| id | pid  | getParentList(id) |+----+------+-------------------+|  1 |    0 | 0/1               ||  2 |    1 | 0/1/2             ||  3 |    1 | 0/1/3             ||  4 |    2 | 0/1/2/4           ||  5 |    2 | 0/1/2/5           ||  6 |    3 | 0/1/3/6           ||  7 |    6 | 0/1/3/6/7         ||  8 |    0 | 0/8               ||  9 |    8 | 0/8/9             || 10 |    8 | 0/8/10            || 11 |    8 | 0/8/11            || 12 |    9 | 0/8/9/12          || 13 |    9 | 0/8/9/13          || 14 |   12 | 0/8/9/12/14       || 15 |   12 | 0/8/9/12/15       || 16 |   15 | 0/8/9/12/15/16    || 17 |   15 | 0/8/9/12/15/17    |+----+------+-------------------+17 rows in set (0.01 sec)