MySQL implements the Connect by feature similar to Oracle

1. Generate Test tables and data:
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 (Ten, ' J ', 8);
Insert into mytest (ID,NODENAME,PID) VALUES (one, ' K ', 8);
Insert into mytest (id,nodename,pid) VALUES (n, ' L ', 9);
Insert into mytest (id,nodename,pid) VALUES (, ' M ', 9);
Insert into mytest (id,nodename,pid) VALUES (+, ' N ', 12);
Insert into mytest (id,nodename,pid) VALUES (+, ' O ', 12);
Insert into mytest (id,nodename,pid) VALUES (+, ' P ', 15);
Insert into mytest (id,nodename,pid) VALUES (+, ' Q ', 15);

2. Create the function:
Delimiter $$
CREATE FUNCTION ' getparentlist ' (rootid varchar ()) RETURNS varchar (1000)
BEGIN
DECLARE sparentlist VARCHAR (1000);
DECLARE sparenttemp VARCHAR (1000);
SET sparenttemp =cast (Rootid as CHAR);
While sparenttemp are not NULL does
IF (Sparentlist is isn't 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. Test verification:
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 |
+----+------+-------------------+
$ rows in Set (0.01 sec)

This article is from the "Webseven" blog, make sure to keep this source http://webseven.blog.51cto.com/4388012/1650540

MySQL implements the Connect by feature similar to Oracle