A SQL implementation of MySQL recursive query

As we all know, the current MySQL version does not support direct recursive query, but through recursive to iterative transformation of the idea, or can be implemented in a sentence of SQL tree recursive query. This is thanks to MySQL allowing the @ variable to be used within the SQL statement. The following is the sample code.

Create a table

CREATE TABLE ' TreeNodes ' (    int--  Node ID    varchar ( node name    int  -- Node Parent ID

Inserting test data

INSERT  into' TreeNodes ' (' id ', ' nodename ', ' pid ')VALUES('1','A','0'),('2','B','1'),('3','C','1'),('4','D','2'),('5','E','2'),('6','F','3'),('7','G','6'),('8','H','0'),('9','I','8'),('Ten','J','8'),(' One','K','8'),(' A','L','9'),(' -','M','9'),(' -','N',' A'),(' the','O',' A'),(' -','P',' the'),(' -','Q',' the'),(' -','R','3'),(' +','S','2'),(' -','T','6'),(' +','U','8');

Query statements

 SELECTId asId,pid asParent ID, levels asProgression between parent to child, paths asParent to Child path from (     SELECTId,pid,@le:= IF(PID= 0,0,           IF(LOCATE (CONCAT ('|'Pid':'),@pathlevel)> 0, Substring_index (Substring_index (@pathlevel, CONCAT ('|'Pid':'),-1),'|',1)+1        ,@le+1)) levels,@pathlevel:=CONCAT (@pathlevel,'|'Id':',@le,'|') Pathlevel,@pathnodes:= IF(PID=0,', 0', Concat_ws (',',           IF(LOCATE (CONCAT ('|'Pid':'),@pathall)> 0, Substring_index (Substring_index (@pathall, CONCAT ('|'Pid':'),-1),'|',1)              ,@pathnodes, PID)) paths,@pathall:=CONCAT (@pathall,'|'Id':',@pathnodes,'|') Pathall fromTreeNodes, (SELECT @le:=0,@pathlevel:="',@pathall:="',@pathnodes:="') VVORDER  bypid,id) srcORDER  byId

The final results are as follows:

    ID Parent ID Parent to child progression parent to child path---------------------------------------1 0 0, 0           2 1 1, 0,1 3 1 1, 0,1 4 2 2              , 0,1,2 5 2 2, 0,1,2 6 3 2, 0,1,3            7 6 3, 0,1,3,6 8 0 0, 0 9 8 1               , 0,8 10 8 1, 0,8 11 8 1, 0,8        12 9 2, 0,8,9 13 9 2, 0,8,9 14 12        3, 0,8,9,12 15 12 3, 0,8,9,12 16 15 4, 0,8,9,12,15        17 15 4, 0,8,9,12,15 18 3 2, 0,1,3 19 2     2, 0,1,2        20 6 3, 0,1,3,6 21 8 1, 0,8        

A SQL implementation of MySQL recursive query